Problem with Report and SQL

[freefour]

Hi,

I have 3 different tables in my database: Invoice, Parts, and Labor. My Invoice form contains 2 subforms Parts and Labor that link to the Parts and Labor subform.

Invoice has a unique ID, the Parts and Labor tables contain the lookedup value of InvoiceID along with their own unique IDs.

When viewing a invoice, I want to launch a report that filters out all the invoice data along with corresponding data in the parts and labor tables.

I use a simple sql statement in the record sounce inner joining by invoice ID.

This works wonderful as long as there is data in all 3 tables that correspond. However, if there is no data in say the labor table, nothing is pulled.


Here is my sql statment:

SELECT DISTINCTROW [Invoice].[InvoiceID, [Invoice].[FixedDescription], [Invoice].[AmountPaid], [Invoice].[PaymentMethod], [Labor].[LaborHours], [Parts].[PartsDescription], [Parts].[PartsQuanity], [Parts].[PartsPrice], [Parts].[PartsTotal] FROM (([Invoice] INNER JOIN [Parts] ON [Invoice].[InvoiceID] =[Parts].[InvoiceID]) INNER JOIN [Labor] ON [Invoice].[InvoiceID] =[Labor].[InvoiceID]);


What do I need to change so if there is no data from, say the Parts table, it will still pull everything else and just leave those blank??

Thanks!

--freefour

 

[freefour]

Duh! I figured it out, LEFT join not INNER join. I really need a newer sequel book.

Thanks anyway.