Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Question Unanswered: Conceptual: query ?/possible table design flaw

    Thanks for reading.

    The real world is making my DB difficult

    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?

    Thanks for taking the time to read this


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    That seems easy or do I miss something?
    SELECT * FROM carNumber
    WHERE [Fleet#] NOT IN ( SELECT [Fleet#] FROM carNumber WHERE Active = Yes );
    Not sure about WHERE Active = Yes, though. If [Active] is a boolean column it should be: WHERE Active = True
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts