QUERY DOES NOT WORK - RETURN ENTRIES IN LIST A NOT IN LIST B

[BlueWolverine]

Hello, I have used this before to find out what is in LIst A that's not in
LIST B. It is not working this time.

SELECT ex_VehicleInventory.[is unique?], ex_VehicleInventory.[Is good
length?], ex_VehicleInventory.Sequence, ex_VehicleInventory.[Full VIN
Number], ex_VehicleInventory.[Tag No#], ex_VehicleInventory.[Model Year],
ex_VehicleInventory.Program, ex_VehicleInventory.[Build Phase],
ex_VehicleInventory.[Manager CDSID], ex_VehicleInventory.Fuel,
ex_VehicleInventory.[In Service], ex_VehicleInventory.[Out of Service Date],
ex_VehicleInventory.[Disposition Method], "ERROR - These vehicles are not
labeled as part of an approved program." AS MSG
FROM ex_VehicleInventory INNER JOIN t_ProgMY ON (ex_VehicleInventory.[Model
Year] = t_ProgMY.ModelYear) AND (ex_VehicleInventory.Program =
t_ProgMY.ProgramName)
WHERE (((ex_VehicleInventory.[Full VIN Number])<>"") AND
((t_ProgMY.ProgramName) Is Null) AND ((t_ProgMY.ModelYear) Is Null));

Here's the high lights because it's a lot of emptiness there.

I want to know what Program/Model Year Combos are in ex_vehinv are NOT in
t_ProgMY. I also don't want it to return any records in ex_vehiINv that have
a blank, "", Empty VIN field.

And then I want to return every column in ex_vehInv that's why there are so
many selects.

Access 2003 on XP Pro.

Thank you. I tried to copy methodology from a wonderful answer I got to a
previous post but it did not work.

Thanks again.

 

[BlueWolverine]

PERFECT! THANK YOU SO MUCH THAT DID IT
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Hello, I have used this before to find out what is in LIst A that's not in
LIST B. It is not working this time.

SELECT ex_VehicleInventory.[is unique?], ex_VehicleInventory.[Is good
length?], ex_VehicleInventory.Sequence, ex_VehicleInventory.[Full VIN
Number], ex_VehicleInventory.[Tag No#], ex_VehicleInventory.[Model Year],
ex_VehicleInventory.Program, ex_VehicleInventory.[Build Phase],
ex_VehicleInventory.[Manager CDSID], ex_VehicleInventory.Fuel,
ex_VehicleInventory.[In Service], ex_VehicleInventory.[Out of Service Date],
ex_VehicleInventory.[Disposition Method], "ERROR - These vehicles are not
labeled as part of an approved program." AS MSG
FROM ex_VehicleInventory INNER JOIN t_ProgMY ON (ex_VehicleInventory.[Model
Year] = t_ProgMY.ModelYear) AND (ex_VehicleInventory.Program =
t_ProgMY.ProgramName)
WHERE (((ex_VehicleInventory.[Full VIN Number])<>"") AND
((t_ProgMY.ProgramName) Is Null) AND ((t_ProgMY.ModelYear) Is Null));

Here's the high lights because it's a lot of emptiness there.

I want to know what Program/Model Year Combos are in ex_vehinv are NOT in
t_ProgMY. I also don't want it to return any records in ex_vehiINv that have
a blank, "", Empty VIN field.

And then I want to return every column in ex_vehInv that's why there are so
many selects.

I think the INNER JOIN should be a LEFT JOIN

 

[Marshall Barton]

Hello, I have used this before to find out what is in LIst A that's not in
LIST B. It is not working this time.

SELECT ex_VehicleInventory.[is unique?], ex_VehicleInventory.[Is good
length?], ex_VehicleInventory.Sequence, ex_VehicleInventory.[Full VIN
Number], ex_VehicleInventory.[Tag No#], ex_VehicleInventory.[Model Year],
ex_VehicleInventory.Program, ex_VehicleInventory.[Build Phase],
ex_VehicleInventory.[Manager CDSID], ex_VehicleInventory.Fuel,
ex_VehicleInventory.[In Service], ex_VehicleInventory.[Out of Service Date],
ex_VehicleInventory.[Disposition Method], "ERROR - These vehicles are not
labeled as part of an approved program." AS MSG
FROM ex_VehicleInventory INNER JOIN t_ProgMY ON (ex_VehicleInventory.[Model
Year] = t_ProgMY.ModelYear) AND (ex_VehicleInventory.Program =
t_ProgMY.ProgramName)
WHERE (((ex_VehicleInventory.[Full VIN Number])<>"") AND
((t_ProgMY.ProgramName) Is Null) AND ((t_ProgMY.ModelYear) Is Null));

Here's the high lights because it's a lot of emptiness there.

I want to know what Program/Model Year Combos are in ex_vehinv are NOT in
t_ProgMY. I also don't want it to return any records in ex_vehiINv that have
a blank, "", Empty VIN field.

And then I want to return every column in ex_vehInv that's why there are so
many selects.

I think the INNER JOIN should be a LEFT JOIN