multiple table query problem

[antonyx]

hi, i have three tables..

Job_Main (table name)
FIELDS
Job Id (PK)
Date
Time
Pickup
Destination

By_Driver (table name)
FIELDS
Driver Id (PK)
First Name
Last Name

Job_By_Driver (table name)
FIELDS
Driver Job Id (PK)
Job Id (FK)
Driver Id (FK)

i use a form with two combo boxes that allow the user to state which driver did which job. when i create a query with the name of the driver from the 'by_driver' table and the date and time of the job from the 'job_main' table.. then my results show that every driver has done every job, rather then taking the information from the connecting 'job_by_driver' table and separating each job to the specific driver, why is this?

 

[antonyx]

ok ive drawn up a single example to illustrate my problem.
here is a screenshot of my three tables, the design and the records.

http://www.heathrowminicab.com/tables.jpg

i use a simple entryform to register a job to a driver.

http://www.heathrowminicab.com/entryform.jpg


so basically i made a query to display the driver name and the details of the job they did.

http://www.heathrowminicab.com/simplequery.jpg

if you look at the data, then driver 'michael jones' with driver id 2 has done 2 of the jobs. however, when the results of the query are displayed then it says that every driver has done every job like so..

http://www.heathrowminicab.com/simplequeryresults.jpg


any suggestions?

 

[Jon K]

You need to put all three tables in the query and properly link the fields between the tables. See image attached.
.

 

[antonyx]

yes that worked thank you, so basically in the future if im to make any queries, as long as i include all the relevant linked tables in the design of the query then there shouldnt be any problems