Expression building 1

[BrianLe]

I need help building a query expression that returns the records that don't meet certain criteria? I have a table "tblSchedule" that includes fields "TrainID", "DateID" (1,2)and "DateTypeID" (1,2,3,4), as well as other fields. There can be multiple records for each TrainID with various DateID's and DateTypeID's. How do I build an expression that (1) returns records that don't have DateID = 2 and DateTypeID =4, and (2) also eliminates all records that have the same TrainID that were eliminated in part (1) but have say DateID =1 and DatetypeID =3?

I've tried the following and it does the first part, but records with matching TrainID are not eliminated.

SELECT tblShedule.TrainID, tblTrain.[Train#], tblShedule.DateTypeID, tblShedule.DateID, tblShedule.DateTime
FROM tblShedule INNER JOIN tblTrain ON tblShedule.TrainID = tblTrain.TrainID
WHERE ((([tblShedule]![DateTypeID])<>2)) OR ((([tblShedule]![DateID])<>4));

Thanks

 

[PHV]

You may try this:
SELECT S.TrainID, T.[Train#], S.DateTypeID, S.DateID, S.DateTime
FROM (tblShedule AS S
INNER JOIN tblTrain AS T ON S.TrainID = T.TrainID)
LEFT JOIN (SELECT TrainID FROM tblTrain WHERE DateID=2 AND DateTypeID=4
) AS X ON S.TrainID = X.TrainID
WHERE X.TrainID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Tried your suggestion but get a syntax error in FROM clause. I'll try to troubleshoot it tomorrow. Thanks

 

[BrianLe]

I can't figure out the syntax problem and also noted my incorrect spelling of "Schedule". Do I have to spell out the table names instead of using the abbreviations "T" and "S"?

Here is tblSchedule

TrainID DateTypeID DateID DateTime
149 1 1 7/13/07 12:00:00 PM
149 2 1 7/14/07 12:00:00 PM
149 1 2 7/20/07 10:00:00 PM
149 2 4 7/23/07 3:00:00 PM
148 2 1 7/14/07 12:00:00 PM
148 1 2 7/20/07 10:00:00 PM

When I run the query I want only the 2 records for TrainID = 148 to be included. Since TrainID=149 is finished (DateTypeId = 2 and DateID =4), no records of TrainID 149 should be included. Thanks

 

[PHV]

Which version of access ?
My suggestion is for ac2k or above.

 

[BrianLe]

Oops. I'm using AC97. Any suggestion for AC97?

 

[PHV]

Any suggestion for AC97?
Sure, at least 2 ;-)
1) Create a query named, say, qryGetFinished:

SELECT TrainID
FROM tblTrain
WHERE DateTypeId = 2 AND DateID = 4

Now your query:

SELECT S.TrainID, T.[Train#], S.DateTypeID, S.DateID, S.DateTime
FROM (tblShedule AS S
INNER JOIN tblTrain AS T ON S.TrainID = T.TrainID)
LEFT JOIN qryGetFinished AS X ON S.TrainID = X.TrainID
WHERE X.TrainID Is Null

2) If you prefer an all-in-one way:

SELECT S.TrainID, T.[Train#], S.DateTypeID, S.DateID, S.DateTime
FROM tblShedule AS S
INNER JOIN tblTrain AS T ON S.TrainID = T.TrainID
WHERE S.TrainID Not In (SELECT TrainID FROM tblTrain WHERE DateTypeId=2 AND DateID=4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Option 2 works great after changing tblTrain in the last "FROM" statement to tblSchedule. Thanks