Please Help

[shaybob1711]

I am currently working on a Job Tracking/Hours Tracking DB for my company and I have run into a major road block. Here is a breakdown of my problem as best I can describe it. (FYI: I am a complete novice when it comes to access)

Tables Involved

tblJobInformation - Primary Key is JobNumber
tblHoursEntry - Includes Field JobNumber
tblQuotes - Includes Field JobNumber

Note: Everything is linked using the JobNumber in each table.

The issue is when I try and run the query for a report before any hours are entered in tblHoursEntry the entire query comes up blank (including fields that are pulling from the other tables) and thus the report is completly blank. I need to be able to run this report before any hours are entered so we can start to filter it through the shop.

I know the issue lies with not all the Job Numbers being in tblHoursEntry but I cant figure out how to bypass that so if there are no hours entered yet for that job the query will default to 0 in that field. I dont want the folks inside to have to make dummy entries into tblHoursEntry .

In the query the fields JobNumber are linked between tblJobInformation and tblHoursEntry.

Any help would be appriciated. I hope this makes sense to someone. Thanks.

 

[Rabbie]

This is because you are using Inner Joins which only return a record if there is a match in both tables. You need to use an outer join which will return records even if there is not one in the other table. On the Query design grid just right click on the join between the tables and you can choose which type of join you want.

 

[shaybob1711]

Rabbie,

Thanks. Its amazing how something so simple can cause days worth of frustration. Thanks again for the quick reply.

 

[Rabbie]

Always happy to help. Glad you got it fixed. Why are other people's problems always easier to fix.