How to filter out records in my query?

[Todd]

I've got a query which looks at a table and returns the
the PrepBIN for the latest prepdate from the table

Here's the SQL:

SELECT PrepVessels.PrepBIN, Max(PrepVessels.PrepDate) AS
MaxOfPrepDate
FROM PrepVessels
GROUP BY PrepVessels.PrepBIN;

This works great, but now I'd like to further filter the
data by also looking at a table called history which has a
field called BIN. If there exists a PrepBIN that matches
any BIN in the History table, then I want my query to
exclude that PrepBIN.

I'm pretty sure that I need to add something like a Where
statement, but I can't seem to get it right.

Can anyone help? Thanks in advance.

 

[John Spencer (MVP)]

You need to add the History table to your query and join the PrepVessels table
to the history table on the Bin fields. The SQL would look something like:

SELECT PrepVessels.PrepBIN,
Max(PrepVessels.PrepDate) AS MaxOfPrepDate
FROM PrepVessels LEFT JOIN History
ON PrepVessels.PrepBIN = History.Bin
WHERE History.Bin Is Null
GROUP BY PrepVessels.PrepBIN;