merging 2 queries

[Susan]

I have 2 separate queries:

a Union query that I've named "UNION" and

a "find duplicates for UNION" query which access has generated for me.

Now that the underlying data for UNION has grown in size, the "find
duplicates for UNION query" freezes when attempting to open it.

The remedy is most likely to incorporate the SQL code of the "find
duplicates for UNION" query into the SQL code for UNION so that the 2 queries
are merged.

How would I accomplish this using the below queries code:

UNION query SQL code:

(SELECT DocumentHeaders.SoldToFax,First( DocumentHeaders.SoldToCompany) As
FCompany, First(DocumentHeaders.DataSource) As FDataSource
,DocumentHeaders.Remove, First(DocumentHeaders.catagoryID) As FCatID,
Min(DocumentHeaders.ID) As MID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax, DocumentHeaders.Remove
HAVING (((DocumentHeaders.SoldToFax)<>"" ) AND ((DocumentHeaders.Remove)<>-1))
UNION
SELECT DocumentHeaders.ShipToFax, First(DocumentHeaders.ShipToCompany) As
FCompany, First(DocumentHeaders.DataSource) As FDataSource,
DocumentHeaders.Remove, First( DocumentHeaders.catagoryID) As FCatID,
Min(DocumentHeaders.ID) As MID
FROM DocumentHeaders
WHERE DocumentHeaders.Remove <>-1
GROUP BY DocumentHeaders.ShipToFax, DocumentHeaders.Remove
HAVING (((DocumentHeaders.ShipToFax)<>"" )))
UNION (SELECT faxnumbers.SoldToFax, faxnumbers.MSoldToCompany,
faxnumbers.DataSource, faxnumbers.Remove, faxnumbers.CatagoryID, faxnumbers.ID
FROM faxnumbers WHERE faxnumbers.Remove<>-1);



"find duplicates for UNION" SQL code:

SELECT UNION.SoldToFax, UNION.FCompany, UNION.FDataSource, UNION.Remove,
UNION.FCatID, UNION.MID
FROM [UNION]
WHERE (((UNION.SoldToFax) In (SELECT [SoldToFax] FROM [UNION] As Tmp GROUP
BY [SoldToFax] HAVING Count(*)>1 )))
ORDER BY UNION.SoldToFax;


thanks in advance!

 

[Lord Kelvan]

i would say in the union query that you shouldnt ahve anmed union
because that is a reservered word change it to a make table query then
run the find duplicates on the make table query

Regards
Kelvan

 

[Susan]

how do I change my current union query to a "make table" query?

 

[Golfinray]

Change the query to design view, click on queries, and change to make-table.