How to filter out records in my query?

  • Thread starter Thread starter Todd
  • Start date Start date
T

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.
 
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;
 
Back
Top