Select records on three criteria 1

[BrianLe]

Can you help me create a query that will return each record of tblSchedule that has a DateTypeId =2 and DateID =4, along with all the records that have the same TrainNo and TrainPart that were included in the first part of the query?

tblSchedule may look like this

TrainNo TrainPart DateTypeID DateID etc.
1 1 1 3
1 2 1 3
1 1 2 4
1 2 2 3
2 1 1 2
3 2 2 4
etc,

I want the query to return records 3 and 6 because they have DateTypeID =2 and DateID =4, and record 1 because it has the same TrainNo = 1 and TrainPart=1 as record 3.

I have the following query:

SELECT S.TrainNo, S.TrainPart, T.TrainNo, S.DateTypeID, S.DateID
FROM tblSchedule AS S INNER JOIN tblTrain AS T ON S.TrainNo = T.TrainNo
WHERE (((S.TrainNo) In (SELECT TrainNo FROM tblSchedule WHERE DateTypeId=2 AND DateID=4)));

It would return records 1,2,3,4,6 because it is selecting similar TrainNo's, not the combination of TrainNo and TrainPart.

I think I have to get TrainPart into the query someplace.

I have the following tables.

tblTrainPart
TrainPartId (key) autonum
TrainPart text (A,B,C,etc.)

tblDateType
DateTypeID (key) autonum
DateType text

tblDatecode
DateID (key) autonum
DateTitle text

tblTrian
TrainNo (key) text
SourceID number
etc.

tblTrainNo
TrainID autonum
TrainNo (key) text

tblSchedule (no key)
TrainNo text
TrainPart text (A,B,C,etc.)
DateTypeId number (1,2)
DateID number (1,2,3,4)
etc.

Should I have another table "tblTrainset" with fields TrainNo and TrainPart?

 

[PHV]

SELECT S.TrainNo, S.TrainPart, T.TrainNo, S.DateTypeID, S.DateID
FROM (tblSchedule AS S
INNER JOIN tblTrain AS T ON S.TrainNo = T.TrainNo)
INNER JOIN (
SELECT TrainNo, TrainPart FROM tblSchedule WHERE DateTypeId=2 AND DateID=4
) AS X ON S.TrainNo = X.TrainNo AND S.TrainPart = X.TrainPart

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

 

[BrianLe]

PH,

I get a "syntax error in FROM clause" when I try your query. I've tried numerous variations in placement of the () but get other syntax errors. I'm using AC97. Any suggestions?

Thanks,

Brian

 

[PHV]

AC97 ...
Create a query named, say, qryGetTrain:
SELECT TrainNo, TrainPart FROM tblSchedule WHERE DateTypeId=2 AND DateID=4

And now, your query:
SELECT S.TrainNo, S.TrainPart, T.TrainNo, S.DateTypeID, S.DateID
FROM (tblSchedule AS S
INNER JOIN tblTrain AS T ON S.TrainNo = T.TrainNo)
INNER JOIN qryGetTrain AS X ON S.TrainNo = X.TrainNo AND S.TrainPart = X.TrainPart

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

 

[BrianLe]

Thanks PH. Now I'm trying to make a query to get the "Unfinished" trains from the tblSchedule, i.e. records 2, 4 and 5 from the above table. I assume I need a qryGetUnfinishedTrain, but I can't figure out what it should look like.