Solved Compare Dates in Two Tables

[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));

 

[KINGOFCHAOS17]

Thank you for tips, I've managed to get the query to work as expected.

 

[Gasman]

Thank you for tips, I've managed to get the query to work as expected.

Care to post the final query sql?
It might help others in the future?

After all that is what these sites are for?

 

[KINGOFCHAOS17]

SELECT [SPOL01 Shelf Life Batches].PartNbr, [SPOL01 Shelf Life Batches].Batch AS [SPOL Batch], [SPOL01 Shelf Life Batches].[Batch Qty] AS [SPOL Batch Qty], [SPOL01 Shelf Life Batches].ExpirationDate AS [SPOL Exp Date], [POS Shelf Life Batches].BatchLot AS [POS Batch], [POS Shelf Life Batches].Qty AS [POS Qty], [POS Shelf Life Batches].ExpiryDate AS [POS Exp Date], DateDiff("d",[SPOL01 Shelf Life Batches].[ExpirationDate],[POS Shelf Life Batches].[ExpiryDate])>30 AS [POS Has Greater Date]
FROM [SPOL01 Shelf Life Batches] LEFT JOIN [POS Shelf Life Batches] ON [SPOL01 Shelf Life Batches].PartNbr = [POS Shelf Life Batches].PartNbr
WHERE (((DateDiff("d",[SPOL01 Shelf Life Batches].[ExpirationDate],[POS Shelf Life Batches].[ExpiryDate])>30)=Yes));

 

[Gasman]

SELECT [spol01 shelf life batches].partnbr,
       [spol01 shelf life batches].batch
       AS [SPOL Batch],
       [spol01 shelf life batches].[batch qty]
       AS [SPOL Batch Qty],
       [spol01 shelf life batches].expirationdate
       AS [SPOL Exp Date],
       [pos shelf life batches].batchlot
       AS [POS Batch],
       [pos shelf life batches].qty
       AS [POS Qty],
       [pos shelf life batches].expirydate
       AS [POS Exp Date],
Datediff("d", [spol01 shelf life batches].[expirationdate], [pos shelf life batches].[expirydate]) > 30 AS [POS Has Greater Date]
FROM   [spol01 shelf life batches]
       LEFT JOIN [pos shelf life batches]
              ON [spol01 shelf life batches].partnbr =
                 [pos shelf life batches].partnbr
WHERE
(( (
Datediff("d", [spol01 shelf life batches].[expirationdate], [pos shelf life batches].[expirydate]) > 30 ) = yes ));

 

[Pat Hartman]

Maybe you shouldn't have the same data in multiple places?