• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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.
0
Tintoman51
Asked:
Tintoman51
  • 2
2 Solutions
 
c1nmoCommented:
Double-click the joins and change to select all records from customer data and only those that match from the other tables.
0
 
telyni19Commented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now