Access queries Remove Duplicate based on value

[caazchinny]

I have two queries, qFabricate01 and qFabricate02, with the only difference is in the field labeled Fabricate. In q01 I am using the date value from the field Delivery and q02 the date value from the field PUDATE. What I would like to do is combine the two, removing the duplicate row, of the line item that contains the Fabricate date in the future.

Ex on these two orders I would only want the two line items that had Fabricate dates of 8/14 and 8/16

Fabricate Delivery PUDate COMMs
08/16/2012 08/16/2012 08/22/2012 T041317
08/22/2012 08/16/2012 08/22/2012 T041317
08/23/2012 08/23/2012 08/14/2012 T045118
08/14/2012 08/23/2012 08/14/2012 T045118

SELECT qOracleOpenOrders.Delivery AS Fabricate, qOracleOpenOrders.Delivery, qOracleOpenOrders.Confirmed, qOracleOpenOrders.EntryDate, qOracleOpenOrders.COMMs, qOracleOpenOrders.BuilderKey, qOracleOpenOrders.Room, qOracleOpenOrders.Status, qOracleOpenOrders.PUDate, qOracleOpenOrders.ESO, qOracleOpenOrders.Market, qOracleOpenOrders.Material, qOracleOpenOrders.ID, qOracleOpenOrders.Comments, qOracleOpenOrders.Requested, qOracleOpenOrders.Remake, qOracleOpenOrders.LTFAB, qOracleOpenOrders.LTJSD, qOracleOpenOrders.Due FROM qOracleOpenOrders

 

[accessAnswer]

To Combine 2 queries together
use

Select * from Query 1
Union
Select * from Query 2 ;

"Union" would delete for you rows in Query 2 that are duplicated in Query 1.

 

[caazchinny]

I want to delete the duplicate item which has a date in the future, not just the item in query2

 

[accessAnswer]

hmmm.

Union- not just deletes duplicates in the second query, but
in general it combines 2 queries and then from the final result it deletes all duplicate lines.

You can put:

Select * from Query 1
Where Fabricate<=Date()
Union
Select * from Query 2
Where Fabricate<=Date();

 

[caazchinny]

I tried that command, but its removing to many items. I have 456 items in each query, but the union is only display 324

 

[jdraw]

For your example:

Backup your table and Try

DELETE from fabricate where fabricate not in
(SELECT TOP 2 Fabricate.Fabricate
FROM Fabricate
ORDER BY Fabricate.Fabricate)
;

 

[caazchinny]

I created a table, this command deleted 863 of 912, way too many

 

[accessAnswer]

I tried that command, but its removing to many items. I have 456 items in each query, but the union is only display 324

This means that you have 324 unique lines ( that means you have this many unique lines with date in the past ( or today).

Just test it. a) take lines that exist before the query
b) see if they are deleted by query

Do you want that to happen? see what is wrong

Check if your Date() comparison works. Sometimes Date() comparison gives random results if Date() and your date are in different Formats.

 

[caazchinny]

I figured out a way to do this, just eliminating the two queries and creating a new one with an if statement for producing the date.