Query matching products sent and received

[ThomasLonsdale]

Hi. I have two tables that I want to match records against one another in a unique way so that the query produces a result showing me all the remaining records that are unmatched i.e. I want to quickly find any products that have been sent but not returned.



The first table contains the relevant sent information:

Sent Date | Product ID | Company | Comments

The second table contains relevant received information:

Received Date | Product ID | Company | Comments

What I want to do is match records in a chronological way so that if for example we had the following events:

Sent 01/01/2015 Product #1
Received 02/01/2015 Product #1

Sent 05/01/2015 Product #1
Received 06/01/2015 Product #1

Sent 08/01/2015 Product #1

The query would match events and display the single last event which has no matching received record (no later return date). I've spent some time on this and I've had some progress but my biggest problem is that my SQL code doesnt match my records such that it pairs Product ID AND obeys the condition that the received date is later than the sent date.

Excuse my poor table/field names as this was my first attempt at a MS access database but currently my SQL code looks like this:

SELECT [New Entry].[Sent Date], [New Entry].Dolavs.Value, [New Entry].Company, [New Entry].Comments, [Received Dolavs].[Received Date], [Received Dolavs].Dolavs.Value, [Received Dolavs].Company, [Received Dolavs].Comments


FROM [New Entry] LEFT JOIN [Received Dolavs] ON [New Entry].Dolavs.Value = [Received Dolavs].Dolavs.Value


WHERE [Received Dolavs].[Received Date] Is Null;

Which gives me exactly what I need EXCEPT it doesn't discriminate the condition required that we only want to match records where the Sent Date is less than the Received date which should leave any records where there is a sent date that is later than any records received dates.

Thanks for your help.

[ItsMe]

Maybe you can create an alias and include an IIf() function. Something like
MyAlias: IIf([Sent Date] < [Received Date], 1, 0)

Add a criteria to your alias
>0