Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    8

    Unanswered: Two table query problem

    Hi all,

    I have 2 Tables:

    Tbl1 - Transport data
    These are the critical fields (there are a few others)
    Date
    Truck ID
    Revenue
    Driver Cost

    Tbl2 - Truck Costs
    Date
    Truck ID
    Service Type
    Service Cost

    BTW - a third tbl - Truck info has the PK for Truck ID and is related to the 2 above Truck ID's

    I'm trying to generate a query to show the revenue and the costs (driver and service) for any given period. The problem lies that if a truck hasn't been serviced for the period, the query does not show the revenue either. conversely, if the truck hasn't been used and has had some service, it will not show.

    What I would like in the query is to show all trucks (even those with nil revenue) and their associated costs.

    How do I do this?

    Thanks in advance and thanks to all those who have assisted me the past.

    Sid

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It sounds like you need to configure the join between the 2 (or 3) tables correctly in the query. Double-click on the line between the 2 tables and select the appropriate join type (i.e. include all records from table xxx and only those records from table yyyy where the joined fields are equal.)
    Last edited by pkstormy; 10-31-07 at 10:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2007
    Posts
    8
    Thanks Pkstormy,

    Right on the money - FYI I was using the Truck ID on the cost and transport table for the join instead of the Truck ID in the Truck table.

    Thanks again

    Sid

  4. #4
    Join Date
    Oct 2007
    Posts
    8
    Hi again!

    I have selected a date parameter in the same query, but after I enter the dates I still get all the data - I have two date fields in the query, 1 for the transport data and 1 for the truck costs.

    Anyone help

    Thanks

    Sid

Posting Permissions

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