SQL NOT IN Query problems

[Doozer1979]

Hello,

I have a list of items that need to be checked on a daily basis. At the end of the day i need to print off a report of all the items that have not been checked that day.

The way that i am attempting to do this is by using a nested SQL NOT IN query but i can't get it to work.

The code that i am using is below.

SELECT Stations.StationName, Stations.StationID, Group.GroupID, Group.GroupName
FROM [Group] INNER JOIN Stations ON Group.GroupID = Stations.GroupID


WHERE (((Stations.StationID) Not In (Select AlertsAM.AlertAMID, Stations.StationID

FROM [group] INNER JOIN Stations ON AlertsAM.StationID = Stations.StationID

WHERE (((AlertsAM.DateOfAction)=[Forms]![frmReports]![txtUncheckedStations])))));


The parameter at the end of the SQL Statement is a text field that the user enters the dtate for the report that they wish to print out.

If its helps explain the code, each station belongs to a group of stations

Does anyone know if i'm on the right track with this?

Many thanks

 

[neileg]

Your subquery has a problem. You're joining [Group] and Stations but selecting fields from AlertsAM.

By the way, group is a bad name for a table because it is a reserved word. I don't that that's influencing this problem, though.

 

[Doozer1979]

Thanks for the tip about the reserved words. I'll change it.


I've revised my query but it's still not giving me the answers i need. To clarify; A list of stations needs to be checked every day, and each time they are checked an alertAMID is generated.

At the end of the day i need to be able to pull out a list off all the stations that haven't been checked that day.

I want to generate a query that looks at the alertsAM and Stations tables and generates a recordset that lists all the stations that do not have an alertAMID on a specified date


This is the code i'm using now

SELECT AlertsAM.AlertAMID, AlertsAM.GroupID, Group.GroupName, AlertsAM.StationID, Stations.StationName, AlertsAM.DateOfAction
FROM [Group] INNER JOIN (AlertsAM INNER JOIN Stations ON AlertsAM.StationID = Stations.StationID) ON Group.GroupID = Stations.GroupID
WHERE AlertsAM.StationID NOT IN


(SELECT Alertsam.StationID from AlertsAM
WHERE (((AlertsAM.DateOfAction)=[Forms]![frmReports]![txtUncheckedStations])));


If anyone can point out what i'm doing round i'd be very appreciated


thank you

 

[Doozer1979]

Thanks for the tip about the reserved words. I'll change it.


I've revised my query but it's still not giving me the answers i need. To clarify; A list of stations needs to be checked every day, and each time they are checked an alertAMID is generated.

At the end of the day i need to be able to pull out a list off all the stations that haven't been checked that day.

I want to generate a query that looks at the alertsAM and Stations tables and generates a recordset that lists all the stations that do not have an alertAMID on a specified date


This is the code i'm using now

SELECT AlertsAM.AlertAMID, AlertsAM.GroupID, Group.GroupName, AlertsAM.StationID, Stations.StationName, AlertsAM.DateOfAction
FROM [Group] INNER JOIN (AlertsAM INNER JOIN Stations ON AlertsAM.StationID = Stations.StationID) ON Group.GroupID = Stations.GroupID
WHERE AlertsAM.StationID NOT IN


(SELECT Alertsam.StationID from AlertsAM
WHERE (((AlertsAM.DateOfAction)=[Forms]![frmReports]![txtUncheckedStations])));


If anyone can point out what i'm doing wrong i'd be very appreciated


thank you

 

[neileg]

If I understand the process, then if the Station hasn't been checked then there is no record in AlertsAM. Because you are using inner joins, this requires there to be a matching record on both sides of the join. That means that any station with no record in AlertsAM is excluded from the dataset so your query will always return no records.

You need to use a left or a right join so that you get all of the Stations and any records from AlertsAM that match.

Now you have a dataset that shows all of the entries in AlertsAM that match the date. But you really need to test for the stations that have no entry in AlertsAM in your query so you need to test for a null, not for a different date.

 

[Doozer1979]

Thats' great i'll try your suggestion!


thank you