Multi Query Problem

[melvis]

Hi all,

I have a database that contains records for all of our clients. One of the fields relates to service type received. The main table that holds all the records has many duplicates, as some clients have more than one service.

I have created separate tables and append queries to filter out the duplicates and have now got 3 tables with unique records.

What I am trying to do now is combine these 3 tables and then take out the duplicates, so I'm left with 1 table that I can then use for a mail merge. I have tried to do it in a similar way to the append query but without any luck.

Doe's anybody have any ideas as to where I am going wrong?

 

[dcx693]

If you have 3 tables and want to combine them without any duplicates, use a Union query. The Union query automatically strips out duplicates. Look it up in the Access online help.

 

[melvis]

dcx693,

Thanks for the suggestion. I created a union query but when I run the query, it returns all the records from all 3 tables.

Here is the SQL statement,

SELECT (Person_Id), (Name), (Surname), (Date_of_Birth), (Address), (Post_Code), (Supplier_Name), (Service_Type), (Start_Date)
FROM (tbl_A_no_duplicates)

UNION SELECT (Person_Id), (Name), (Surname), (Date_of_Birth), (Address), (Post_Code), (Supplier_Name), (Service_Type), (Start_Date)
FROM (tbl_B_no_duplicates)

UNION SELECT (Person_Id), (Name), (Surname), (Date_of_Birth), (Address), (Post_Code), (Supplier_Name), (Service_Type), (Start_Date)
FROM (tbl_C_no_duplicates);

I was kind of hoping that it would just return 1 record per client, regardless of whether they have a record under table A, B or C.

Can you see where I'm going wrong?

Cheers.

 

[dcx693]

You only need to select those fields that give you the unique combination of info you want. In your case, you just want address info. You probably don't need the Supplier_Name and Service_Type fields, for example.

You also don't need to enclose those names within parentheses - unless you just want to.

 

[melvis]

Cheers.

The union query has worked, although I've still got 2 clients that pop up with a duplicate record.

If I use:

SELECT (Person_Id), (Name), (Address)
FROM (tbl_A_no_duplicates)

UNION SELECT (Person_Id), (Name), (Address)
FROM (tbl_B_no_duplicates)

UNION SELECT (Person_Id), (Name), (Address)
FROM (tbl_C_no_duplicates)

Then I get 194 records, which is correct, but if I try and add (Supplier_Name), then I get 196.

Problem is, I need to know the Supplier_Name as well as the other information. I'm thinking that it might just be best if I create a Make-Table query from the Union query and then delete these two records manually.

Sorry to be a pain!