Access Reports

Hi
I want to bring data from 3 seperate tables into a single report in Access 2010.
Table 1 contains customer data, name address etc, Table 2 contains the financial aspects of the job, labour charges etc, and table 3 is a list of parts used on the job.
I have used a select query to get the information form each table onto the report, the tables are linked by the job number.
The problem I have is that if a job has been done and no parts were used, I don't get any information about that job on the report at all.
I guess I have not created the relationships properly or perhaps the grouping of the report is not correct, I tried to group by the job number but still get nothing if no parts were used.
Tintoman51Asked:
Who is Participating?
 
c1nmoConnect With a Mentor Commented:
Double-click the joins and change to select all records from customer data and only those that match from the other tables.
0
 
telyni19Connect With a Mentor Commented:
Can you give the SQL of the query you are using, or show the query design?

From what you've said so far, I suspect you are using INNER (two-way) joins, which will only return records if the tables on both sides of the join contain information. To get jobs that have no parts, you need to use a LEFT or RIGHT (one-way) join so that all records are returned from the jobs table regardless of whether any parts are returned from the parts table.
0
 
Tintoman51Author Commented:
I tried the suggestion by c1nmo but it didn't make any difference, the SQL code attached does show an inner join but I don't know how to change this
SELECT Customers.[Job Number], Customers.[Customer Name], Customers.Address, Customers.[Post Code], Customers.[Vehicle Make], Customers.[Vehicle Model], Customers.[Registration Number], Customers.Colour, Customers.[Job Description], PartsList.Quantity, PartsList.Description, PartsList.[Selling cost], Finance.[Labour charge], Finance.[Paint Materials], Finance.[Sale Type], Finance.[Invoice Total]
FROM (Customers INNER JOIN PartsList ON Customers.[Job Number] = PartsList.[Job Number]) INNER JOIN Finance ON Customers.[Job Number] = Finance.[Finance Job Number];

Open in new window

0
 
Tintoman51Author Commented:
Ok thanks to you both, this now works as you suggested.
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.