Select column as true if is exists in another table

[Mike R]

Hello Everyone! I hope you can assist...
I have 2 tables. One with a full list of Bus numbers (BUSPMT) with default unselected Out of Service OOS column and One with a list out buses (OOST) that are OOS. I need a complete list of the first table BUSPMT with the OOS buses selected as TRUE.
I tried to do this with JOINs and Sub-Queries without success.

BUSPMT:	Table
BUS	Location	Service	OOS
100	SMF	Fixed	FALSE
101	SMF	Fixed	FALSE
102	IBMF	Fixed	FALSE
103	IBMF	Para	FALSE
104	IBMF	Para	FALSE
105	SMF	Para	FALSE
106	SMF	Fixed	FALSE
107	SMF	Fixed	FALSE
OOST:	Table
BUS	Contractor	OOS
103	Para	TRUE
107	Fixed	TRUE
Desired Result:
BUSPMTQuery:	Query
BUS	Location	Service	OOS
100	SMF	Fixed	FALSE
101	SMF	Fixed	FALSE
102	IBMF	Fixed	FALSE
103	IBMF	Para	TRUE
104	IBMF	Para	FALSE
105	SMF	Para	FALSE
106	SMF	Fixed	FALSE
107	SMF	Fixed	TRUE

Thank you for any assistance!

[moke123]

Why 2 tables? Couldn't you just update the first table for OOS?

[Mike R]

That is originally what I was doing but vehicles had to be spot checked daily and updated. The purpose of this is to not list vehicles needing maintenance that is in another query not mentioned. It would excluded OOS vehicles. Our IT dept has compiled data from 4 seperate souces that they consolidate for a report. I got them to drop me a link to this report (OOST) it is now automated so no one in my dept needs to spot check anymore.

So.. the first table is a list of all active vehicles that I use for validation (I left out 95% of the fields in my example).
the secone is a list of only out of service OOS coaches. So i really need this list combined somhow showing all vehicles with only OOS checked.

If the OOST table listed all vehicles like the BUSMPT table does I would not have an issue.

EDIT: So everytime the maintenance report is opened for work due, It will automatically update the coaches that are OOS and only show work that is available.

[Mike R]

Someone with a simlilar issue on a different forum had this request below, But I kept getting SQL errors trying to make it work for myself:
Members table:

Members table:
id	name	phone
1	Daniel	123456789
2	Liam	123456789
3	Lucas	123456789
Services table:
MID	profile	lastSeen
1	2	8/13/2014 14:23
3	1	8/12/2014 15:29
I try to achieve this result:
id	name	services
1	Daniel	TRUE
2	Liam	FALSE
3	Lucas	TRUE

SELECT members.id, members.name,

       IF(services.mid IS NULL, FALSE, TRUE) as services

FROM members

LEFT JOIN services ON (members.id = services.mid)

[Welshgasman]

Could use a union perhaps.
First sql could use not in table 2, then second union would be in table 2?

[Micron]

I would have a status field and a status date field to show when a status was set. If that is not important, then the OOS field should be a date field as Y or N doesn't tell you much. Then again, if you really left out 95% then you must have around 100 fields in that table, so that is a bit of a red flag if this is about electrical bus bars and still maybe if it's about buses that are vehicles.

EDIT -
OOS data should not be in it's own table.
BTW, by status I was including OOS as that is what it really is.

[Mike R]

There are 53 fields in BusPMT. Its a data table.
OOST has 18 fields.

[madpiet]

Wow, now I remember why I don't like the query UI in Access. =(

SELECT pmt.bus, pmt.Loc, pmt.Svc, pmt.OutOfSvc, oos.OOS,
ShowOOS = IIF(oos.OOS IS NULL,'False','True')
FROM BusPMT pmt
LEFT JOIN OutOfSvc oos
ON pmt.Bus = oos.Bus

[Mike R]

I actually do have a report date for OOST, but not for BUSPMT

Bus#	Report_Date	Contractor	OOS
835	07/23/2024	TRANSDEV-SMF	TRUE
836	07/23/2024	TRANSDEV-SMF	TRUE
837	07/23/2024	TRANSDEV-SMF	TRUE

[isladogs]

Wow, now I remember why I don't like the query UI in Access. =(

Unfortunately that's not valid SQL ...try ....

SELECT pmt.bus, pmt.Loc, pmt.Svc, pmt.OutOfSvc, oos.OOS,
IIf (oos.OOS IS NULL,'False','True') AS ShowOOS
FROM BusPMT pmt
LEFT JOIN OutOfSvc oos
ON pmt.Bus = oos.Bus;

[Mike R]

@isladogs, Thank you I will try this! About out of time here at work on my Friday. Will try at home throughout this weekend and let you know...

[Mike R]

Unfortunately that's not valid SQL ...try ....

SELECT pmt.bus, pmt.Loc, pmt.Svc, pmt.OutOfSvc, oos.OOS,
IIf (oos.OOS IS NULL,'False','True') AS ShowOOS
FROM BusPMT pmt
LEFT JOIN OutOfSvc oos
ON pmt.Bus = oos.Bus;

I was finally able to get back to the today to test out. I made new takes to keep everything "apples to apples" for testing it out. I got it all figured out Thank you so much everyone for your imput and support!

SELECT pmt.bus, pmt.Loc, pmt.Svc, oos.OOS,IIf(oos.OOS Is Null,'False','True') AS ShowOOS
FROM pmt LEFT JOIN OutOfSvc AS OOS ON pmt.Bus = oos.Bus;

My final result will be a bit different based on my actual tables but this was the answer! Thanks again!