Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006

    Exclamation Unanswered: Date issues in table/query?

    Hi, I know this is probably simple but hope someone can steer a neophyte in the right direction here, please.

    Our fleet db tracks expenses for fleet vehicles (fuel, maintenance and so on) and when the monthly Excel reports come in we invoice the branch that is assigned the vehicle.

    In the past the vehicles stayed in one branch for the whole lease period so the branch# was a field in the vehicle table. Now they move between branches so the structure is changed:

    tableVehicle: VehID (autoNumber), VehLeaseNo (text), VehDescr
    tableBranch: BranchID(autoNumber), BranchNo (text), BranchName etc.
    tableVehAssignment: VAssignID (autoNumber), fkVehID, fkBranchID, StDate, EndDate
    tableTransactions:TransID (autoNumber), TransDate, TransDescr, Amount, fkVehID

    But now with the changing assignments I don't quite know how to find out which expense goes to which branch. Does the BranchID field of my query have to have If statements for every single vehicle? (If VehID=1 AND StDate<=05/01/2006, BranchID=2, otherwise BranchID=17)

    If so, how would I string them all together in one statement properly?

    Thanks for any suggestions!

  2. #2
    Join Date
    Jun 2006

    SQL help

    Should have clarified this a bit - I know how to get the right branch number for one vehicle using the Where TransDate Between StDate and EndDate, my question is how to do that efficiently for all vehicles and all expenses listed on the report.
    Thanks for any assistance you can offer here!

Posting Permissions

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