Solved

Access Reports

Posted on 2011-09-02
4
293 Views
Last Modified: 2012-05-12
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
Comment
Question by:Tintoman51
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
c1nmo earned 250 total points
ID: 36474484
Double-click the joins and change to select all records from customer data and only those that match from the other tables.
0
 
LVL 12

Assisted Solution

by:telyni19
telyni19 earned 250 total points
ID: 36474492
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
 

Author Comment

by:Tintoman51
ID: 36474616
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
 

Author Closing Comment

by:Tintoman51
ID: 36474643
Ok thanks to you both, this now works as you suggested.
Thanks again
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question