The need: I have vehicles in a fleet. Vehicles are assigned a "Fleet#". Vehicles get sold, wrecked and removed from fleet. New vehicles get added. **BUT** my user insists on re-using the Fleet#'s. So, Old vehicle out, New vehicle get's the old car's Fleet#.
I created a main table that houses the majority of the vehicle info. I created a carNumber table to house the FleetNumbers. In the main table there is a boolean field to take care of Vehicle Status "Active" (in fleet), "Inactive" (removed from fleet).
Problem is, I want to generate a report that shows what Fleet#s are NOT in use at the moment. But my main table ends up looking like:
I want to exclude the records with "active" status (mainTable) in my query and display the remaining Fleet numbers that are available in the fleet numbers table.(tblCarNum) But I can't wrap my head around the repeating Fleet Numbers and two statuses. Because some "inactive" vehicles have the same Fleet# as some "active" vehicles.
So I must have a basic design flaw in my table structure... right?