Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Avoid doubling (2000)

    Basic design confusion: 3 tables: trucks, trips, and fuel, all linked by truckID. I can get a listing of all trips / truck or all fuel / truck, but I need a report that has trips and fuel / truck. If I put those in the same query, I get a fuel amount for each trip. I understand why it happens, but I haven't thought of what to do to get around it. It must be simple, but I'm drawing a blank.

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Avoid doubling (2000)

    What is your objective? Is it to determine how much fuel was used each trip? In any event it seems likely that you need some sort of association between fuel and trips - otherwise you would get all fuel and all trips for each truck if you create a query. It would be pretty simple if eacy refueling occurred at the beginning (or end) of a trip. Mileage would seem to be the most likely common denominator.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Avoid doubling (2000)

    I am not relating fuel per trip.
    I need to produce a log for each truck - one that lists, in chronological order, trips taken and fuel purchase by date for each truck. I can get a report with truck info in the detail and subreports for fuel and trips in the group footer. It works, but since those are 2 subreports, there is no way to look at a summary of one day's activity - you have to check 2 areas. (I'm trying to match the existing report. It lists dates and trips. There are several trips / day. There may be more than one fuel purchase per day. When fuel is purchased, the fuel amounts is simply included on one of the lines for that date.) [I HATE trying to match existing reports!]

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Avoid doubling (2000)

    Ah - we've always had a report like this, and we still want one like this! Not the first time I've heard that.

    How about if you put the truck from a query that joins trip info so that you get all the trip dates (use the unique records property for the query) and put the truck info info into the header of the report (or a page header), and then put the subreports in the detail section, with an order by date, and join the subreports both on TruckID and on Date. That presumes that you don't buy fuel on days you don't make trips - if that's not the case, then you may need a Union query to get all of the dates with activity for either fuel or trips. Hope this makes sense and I haven't missed something important.
    Wendell

Posting Permissions

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