Hi all, this is my first post here and im hoping one of you can help me! :).

Basically I have a report which pulls up a number of fields from different tables that basically show a work number and what the value of the materials used in the job cost. It works fine. Now when I go to add the labour table into the query, it instantly cuts out alot of the records. Now im 'assuming' this is due to the fact that not every job could have labour spent on it, that it is simply not showing records that do not have any labour hours associated with it. Therefore I want to be able to call up all the information as I did previously, but be able to show the hours spent on a job (if any). This is not my database and Ive spent a good day or so staring at it and trying multiple things but I cant seem to pull up the information correctly. There is a relationship between the tables, but I am puzzled why the information it pulls up when I add the labour table is so different.

Thanks for any help.

If you want all records that do, or do not, have any labour associated with it, you need to use an Outer Join to select all records from the one table and only those records that have labour in labour. That way you will get all the appropriate records.

Thanks for the input Bob. Right Ill show you what Ive got:

SELECT [Insulation WO].[Work Order], [Materials Used].[Material Identifier], [Materials Used].Qty, [Materials Used].Date, [Material List].Unit, [Material List].Rate, [Insulation WO].[Unique Identifier] FROM ([Insulation WO] INNER JOIN ([Material List] INNER JOIN [Materials Used] ON [Material List].[Material Identifier] = [Materials Used].[Material Identifier]) ON [Insulation WO].[Unique Identifier] = [Materials Used].[Unique Identifier]) INNER JOIN [Labour Allocation] ON [Insulation WO].[Unique Identifier] = [Labour Allocation].[Unique Identifier];

Now Im assuming I would make this one the 'OUTER' Join? That came up with an Error about the FROM Clause (Sorry my SQL is not very good). Hopefully you will be able to see the problem from that... hopefully lol.

Sorry to bump this, but Ive even been to my college lecturer and she couldnt help me!

If theres anything more anyone needs to see to solve this puzzle, please let me know!. The problem sounds like should be simple, but it just seems when all the tables are brought together in the query builder, the data just doesnt group together properly. Its driving me mad!!. Help :(

Can you post the database? Check here for how to:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587