Compare Dates in Two Tables (1 Viewer)

[KINGOFCHAOS17]

Good Afternoon. I need some assistance please. I have two tables (SPOL and POSSL) containing shelf-life expiration dates for various products. The dates are stored in a field named ExpDate and both have a common field of PartNbr to match against. I need the query to compare the dates between the two tables but only show results where the record or records in POSSL are 30 days or greater than SPOL. Both tables might have multiple entries against the same PartNbr. Appreciate any and all assistance.

 

[plog]

This sounds pretty straightforward. What have you tried? What are your issues? If its subtracting the dates, the function you will need to use is DateDiff():

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

Give it a shot and if it doesn't work post back your SQL here and give us some sample data to demonstrate how it isn't working.

 

[theDBguy]

Both tables might have multiple entries against the same PartNbr.

That complicates things a bit, I think. Would you mind posting some sample data from both tables showing the multiple entries of PartNbr and then show us the result you're expecting from the query? Thanks.

 

[June7]

30 days or greater than which SPOL record - the most recent for each PartNbr? Maybe like:

SELECT POSSL.PartNbr, ExpDate
FROM (SELECT SPOL.PartNbr, Max(SPOL.ExpDate) AS MaxExpDate
            FROM SPOL
            GROUP BY SPOL.PartNbr) AS Query1 
INNER JOIN POSSL ON Query1.PartNbr = POSSL.PartNbr
WHERE (((DateDiff("d",[ExpDate],[MaxExpDate])>30)=True));