Combining Data that is not the same date

[Guest]

Hi, I have two tables that have the same columns (LadNo, InspDate, InspResult), one table (LinkedInventory) comes from a linked table that is filled by a handheld application, the other table (Inventory) is filled by the linked table when new records are found and the dates are not the same. I can get the new record part by comparing the Inventory.ladno where it is null. The part that I am having trouble with is the date part. Here is the data as follows

LinkedInventory Tabl
LADNO INSPDATE INSPRESUL
L0001 2/22/2004 Pas
L0001 2/23/2004 Pas
L0002 2/22/2004 Pas
L0003 2/23/2004 Pas
L0005 2/22/2004 Fai
L0001 2/24/2004 Pas
L0002 2/24/2004 Pas
L0003 2/24/2004 Pas
L0004 2/24/2004 Pas
L0006 2/24/2004 Pas

Inventory Tabl
LadNo InspDate InspResul
L0001 2/22/2004 Pas
L0001 2/23/2004 Pas
L0002 2/22/2004 Pas
L0003 2/23/2004 Pas
L0005 2/22/2004 Fai

using this query
SELECT tblLinkedInventory.LADNO, tblLinkedInventory.INSPDATE, tblLinkedInventory.INSPRESUL
FROM tblLinkedInventory LEFT JOIN tblInventory ON tblLinkedInventory.LADNO = tblInventory.LadN
WHERE (((tblInventory.InspDate) Not Like [tblLinkedInventory].[Inspdate])) OR (((tblInventory.LadNo) Is Null))

I get the following results

LADNO INSPDATE INSPRESUL
L0001 2/22/2004 Pas
L0001 2/23/2004 Pas
L0001 2/24/2004 Pas
L0001 2/24/2004 Pas
L0002 2/24/2004 Pas
L0003 2/24/2004 Pas
L0004 2/24/2004 Pas
L0006 2/24/2004 Pas

i want to get the following though where these are the new records

LADNO INSPDATE INSPRESUL
L0001 2/24/2004 Pas
L0001 2/24/2004 Pas
L0002 2/24/2004 Pas
L0003 2/24/2004 Pas
L0004 2/24/2004 Pas
L0006 2/24/2004 Pas

Any Help on this will be appreciated

 

[Guest]

I made a mistake on the result that I was hoping to get, i want to get the following

LADNO INSPDATE INSPRESUL
L0001 2/24/2004 Pas
L0002 2/24/2004 Pas
L0003 2/24/2004 Pas
L0004 2/24/2004 Pas
L0006 2/24/2004 Pas

I missed a row that it was repeating and I am not sure if my first reply went through.

 

[John Spencer (MVP)]

Try

SELECT tblLinkedInventory.LADNO, Max(tblLinkedInventory.INSPDATE) MaxDate, tblLinkedInventory.INSPRESULT
FROM tblLinkedInventory LEFT JOIN tblInventory ON tblLinkedInventory.LADNO = tblInventory.LadNo
WHERE (((tblInventory.InspDate) Not Like [tblLinkedInventory].[Inspdate])) OR
(((tblInventory.LadNo) Is Null))
GROUP BY tblLinkedInventory.LADNO, tblLinkedInventory.INSPRESULT

 

[Guest]

Thanks for your response. That does work.