Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    13

    Unanswered: Access 2007 SQL Question

    Hey guys, this is my first post, i normally do a lot of googling and try to solve a problem by finding a similar one, only this time i have had no luck.

    I have 4 Tables:

    tblGasReceipts
    tblGasReceiptsFOP
    tblVehicle
    tblEmployeesDriverNumber

    I used the query builder to do all the Joins, and aliased everything to make it readable

    Code:
    SELECT tGR.ReceiptNumber, tGR.Date, tGR.Time, tGR.VehicleID, tGR.EmployeeID, tGR.Liters, tGR.GasAmount, tGR.ManualGasGST, tGR.GasGST, tGR.ManualGasPST, tGR.GasPST, tGR.OdometerReading, tGR.FOPID, tGR.CarWashAmount, tGR.ManualCWGST, tGR.CarWashGST, tGR.ManualCWPST, tGR.CarWashPST, tGR.OtherAmount, tGR.ManualOtherGST, tGR.OtherGST, tGR.ManualOtherPST, tGR.OtherPST, tGR.KMPerLiter, tGR.Total, tGR.DateTimeCreated, tGR.Comments, 
    IIf(Len([Vehicle])=6,'0' & [Vehicle],[Vehicle]) AS VehicleSort, '**' & Right([Account],4) AS FOP, FormatDateTime([Date],2) & ' ' & FormatDateTime([Time],4) AS [Date and Time], tblEmployeesDriverNumber.DriverNumber
    FROM tblEmployeesDriverNumber, tblGasReceiptsFOP INNER JOIN (tblVehicles INNER JOIN tblGasReceipts AS tGR ON tblVehicles.VehicleID = tGR.VehicleID) ON tblGasReceiptsFOP.FOPID = tGR.FOPID;
    What i'm trying to do is to grab the DriverNumber from tblEmployeesDriverNumber where the tblGasReceipts.Date (The receipt date) falls between tblEmployeesDriverNumber.HireDate and tblEmployeesDriverNumber.TerminatedDate

    Here's what i've tried to do, but it doesn't work as access doesn't recognize tGR.Date (a pop up comes up and i have to enter a date)

    Code:
    SELECT tGR.ReceiptNumber, tGR.Date, tGR.Time, tGR.VehicleID, tGR.EmployeeID, tGR.Liters, tGR.GasAmount, tGR.ManualGasGST, tGR.GasGST, tGR.ManualGasPST, tGR.GasPST, tGR.OdometerReading, tGR.FOPID, tGR.CarWashAmount, tGR.ManualCWGST, tGR.CarWashGST, tGR.ManualCWPST, tGR.CarWashPST, tGR.OtherAmount, tGR.ManualOtherGST, tGR.OtherGST, tGR.ManualOtherPST, tGR.OtherPST, tGR.KMPerLiter, tGR.Total, tGR.DateTimeCreated, tGR.Comments, IIf(Len([Vehicle])=6,'0' & [Vehicle],[Vehicle]) AS VehicleSort, '**' & Right([Account],4) AS FOP, FormatDateTime([Date],2) & ' ' & FormatDateTime([Time],4) AS [Date and Time], tDN.DriverNumber
    FROM tblGasReceiptsFOP INNER JOIN ((tblVehicles INNER JOIN tblGasReceipts AS tGR ON tblVehicles.VehicleID=tGR.VehicleID) LEFT JOIN (
    SELECT tblEmployeesDriverNumber.DriverNumber 
    FROM tblEmployeesDriverNumber 
    WHERE tGR.Date Between tblEmployeesDriverNumber.HireDate and IIF(isnull(tblEmployeesDriverNumber.TerminatedDate),DateAdd("yyyy",100,tblEmployeesDriverNumber.HireDate),tblEmployeesDriverNumber.TerminatedDate)
    ) AS tDN 
    ON tGR.EmployeeID=tDN.EmployeeID) ON tblGasReceiptsFOP.FOPID=tGR.FOPID;
    Sounds a little confusing, but i've attached a picture to show this. Any help would be greatly appreciated
    Attached Thumbnails Attached Thumbnails Query.JPG  
    Last edited by preacher23; 01-17-10 at 06:29. Reason: almost found a solution

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    have you tryed breaking down the query

    What I do is build the query so that they read off and other query

    eg

    query1 read tables get it 80%-90% right

    then query2 read query1 to get the rest of the DATA
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2010
    Posts
    13
    you're right i broke the problem down and was able to come up with:

    Code:
    SELECT tGR.*, (SELECT tblEmployeesDriverNumber.DriverNumber
    FROM tblEmployeesDriverNumber
    WHERE (((tGR.Date) Between [HireDate] And IIf(IsNull([TerminatedDate]),DateAdd("yyyy",100,[HireDate]),[TerminatedDate])) and tblEmployeesDriverNumber.EmployeeID=tGR.EmployeeID) ORDER BY [HireDate] DESC) AS DriverNumber
    FROM tblGasReceipts AS tGR;
    Do you think that's an efficient way of doing it?

    Edit: There's only one problem, this query is not updatable. Is it because of the nested select ? If so, is there a way around it?
    Last edited by preacher23; 01-18-10 at 02:32. Reason: New problem discovered

Posting Permissions

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