I've built a 'One to Many' join on two tables in Access 2000. The main table is linked to the second using the ID fields. I have added some data to the second table and viewed the results in Access. The data appears against the correct records. Those records that have no data in the second table appear blank as they should. So, everything appears OK in Access.

When I test the SQL statement in UD, the records in the second table appear in every record in the main table, e.g. the main table (clientconfigs) contains company information. The second table contains(clientcontacts) contains contact details such as names, phone numbers etc. I have created two records in the clientcontact table against respective company records in clientconfigs. The SQL query in UD displays the contact data from clientcontacts in all the clientcontact fields resulting in joe bloggs for example working for several different companies at once.

My SQL statements uses the SELECT statement followed by the fields from each table. It then completes with a FROM and WHERE statement(below). The WHERE statement contains the variable MMColParam that contains a default value of % and run time is Request.QueryString("ID"

Can anyone tell me why the clientcontact data is appearing in all clientcontact fields?

FROM clientconfigs, ClientContacts
WHERE ID LIKE 'MMColParam'

Thanks very much in advance.

Davy

Davy

Your SELECT statement contains no JOIN,

I find it easiest to create Queries in MS Access (or SQL Server). Use Create query in Design View to build you Query, then flip into SQL view, and copy/paste the SQL code into the Recordset dialogue box in UD.

You can take a look at my Tutorial, it's not specifically about Queries but they are covered in some detail in Sections 8 and 10.

www.tdsf.co.uk/tdsfdemo/default.htm


Regards

Owen.