Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing table/query (Access2k)

    Well I'm not sure I'll be able to describe this properly, but here goes;

    We're getting an automatic download into a couple of tables from our fork lift truck (FLT) fleet. Amongst other things they show are the drivers log ons, dates, hours used, etc. We have another table with the complete list of drivers as well.

    What we're trying to do is create a report (via a query) to show if a driver has driven, at all, during the last 6 months. So what we're looking at is comparing the drivers table with the downloaded data in one of the other tables, but as this is only a new system, we can't seem to come up with the list, because there's a few drivers who have yet to use the trucks. Because of this, we can only find the ones who HAVE driven the FLTs.

    Ideas any one?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Comparing table/query (Access2k)

    When you join the 2 tables in the query you have to use an outer join to list all the drivers whether they have driven the trucks or not.
    eg.
    SELECT D.DriverName, T.TripDate, etc...
    FROM Drivers as D LEFT JOIN Trips as T ON D.DriverName = T.DriverName;

    You will have to use your own names of the joined fields and DriverName and TripDate etc.

    HTH
    Pat

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Comparing table/query (Access2k)

    I agree completely with Pat's comments.

    If you build the query using the qbe grid rather than by writing sql, you can achieve the same result by double clicking on the join line between the tables, in the grid. You will be given three choices about the type of join you want. Choose the "all drivers" option.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing table/query (Access2k)

    Thanks for that gents, it worked a treat.

Posting Permissions

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