Report based on multiple tables

[Pimped]

Hi guys

The issue is with the following:

Job can have many products and many services (labour)

So I have 3 tables, Job, Job Items and Job Labour Link.

How do I make a report that allows me to list all the products for a Job and all the Labour for a job in the same report/page?

I'm sure it has to do with the underlying query first, but I'm not too sure what to do as it is just all over the place.

I can do it properly if I have labour or products in the report, but when I go for multiple, it is all over the place in both query and report.

Here is my query:

Invoice Data 1

SELECT Job.*, [Job Labour Link].[Labour ID], [Job Labour Link].[Price Per m2]
FROM Job LEFT JOIN [Job Labour Link] ON Job.[Job #] = [Job Labour Link].[Job #];

Invoice Data 2

SELECT [Invoice Data 1].*, [Job Items].*
FROM [Invoice Data 1] LEFT JOIN [Job Items] ON [Invoice Data 1].[Job #] = [Job Items].[Job #];

Invoice Data

SELECT [Invoice Data 2].[Invoice Data 1].[Job #], [Invoice Data 2].[Area Size], [Invoice Data 2].[Labour ID], Labour.[Labour Name], [Invoice Data 2].[Price Per m2], [Invoice Data 2].[Product ID], Product.[Main Category], Product.[Sub Category], Product.[Product Name], Product.[Product Specification 1], Product.[Product Specification 2], Product.Colour, Product.Size, [Customers Extended].[Customer Name], [Customers Extended].[Address 1], [Customers Extended].[Address 2], [Customers Extended].[Address 3], [Customers Extended].Town, [Customers Extended].City, [Customers Extended].Postcode
FROM [Customers Extended] INNER JOIN (([Invoice Data 2] INNER JOIN Labour ON [Invoice Data 2].[Labour ID] = Labour.[Labour ID]) INNER JOIN Product ON [Invoice Data 2].[Product ID] = Product.[Product ID]) ON [Customers Extended].[Customer ID] = [Invoice Data 2].[Customer ID];

That gives me each labour in any job and repeats the labour for each product so I have many rows until the listing is complete.



Can anyone point me in the right direction?

Thanks

[Pimped]

For anyone that has the same problem, I got around this by making 3 seperate queries which all had the same amount of fields. All the fields were called the same name but each obviously took its data from a query or table which I set individually in each query.

Finally, I used a UNION ALL query to union all the queries together to show what I wanted:

SELECT *
FROM qry1
UNION ALL
SELECT *
FROM qry2
UNION ALL
SELECT *
FROM qry3;