Stuck on Join Query

[Pimped]

Hey Guys



I'm stuck on a Join query. I have this info I dug up from my searching:

http://www.eggheadcafe.com/software/...on-3-tabl.aspx

But I need to apply it to my complex query. can anyone help me?

The normal query takes the estimate table and then takes the products query and labour query to give me a grand total list that will show everything correctly IF there are products AND labour for the estimate.

I want to show ALL estimates whether or not they have products and labour.

Here is my SQL as made by Access:

SELECT 
Estimate.[Estimate #], 
[Estimate Items Total].[Product Total], 
[Estimate Items Total].[Product Discount Total], 
[Estimate Items Total].[Estimate Items Subtotal] AS [Products Subtotal], 
[Estimate Items Total].[Estimate Items VAT Total] AS [Products VAT Total], 
[Estimate Labour Totals].[Labour Total], 
[Estimate Labour Totals].[CIS Total], 
[Estimate Labour Totals].[Labour Subtotal], 
[Estimate Labour Totals].[Labour VAT Total], 
(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100 AS [Retention Amount], 
CCur(([Estimate Items Subtotal]+[Labour subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100) AS [Retention Total],
CCur([Estimate Items Subtotal]+[Labour Subtotal]-((([Estimate Items Subtotal]+[Labour Subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100))) AS [Estimate Subtotal], 
CCur([Estimate Items VAT Total]+[Labour VAT Total]-(((([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)*(15/100))) AS [Estimate VAT Subtotal], 
CCur(([Estimate Items Subtotal]+[Labour Subtotal]-((([Estimate Items Subtotal]+[Labour Subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)))+([Estimate Items VAT Total]+[Labour VAT Total]-(((([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)*(15/100)))) AS [Estimate Grand Total]



FROM 
[CIS Standard], 
(Estimate INNER JOIN [Estimate Items Total] ON Estimate.[Estimate #] = [Estimate Items Total].[Estimate #]) INNER JOIN [Estimate Labour Totals] ON Estimate.[Estimate #] = [Estimate Labour Totals].[Estimate #]

GROUP BY 

Estimate.[Estimate #], 
[Estimate Items Total].[Product Total], 
[Estimate Items Total].[Product Discount Total], 
[Estimate Items Total].[Estimate Items Subtotal], 
[Estimate Items Total].[Estimate Items VAT Total], 
[Estimate Labour Totals].[Labour Total], 
[Estimate Labour Totals].[CIS Total], 
[Estimate Labour Totals].[Labour Subtotal], 
[Estimate Labour Totals].[Labour VAT Total], 
(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100, 
CCur(([Estimate Items Subtotal]+[Labour subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100), 
CCur([Estimate Items Subtotal]+[Labour Subtotal]-((([Estimate Items Subtotal]+[Labour Subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100))), 
CCur([Estimate Items VAT Total]+[Labour VAT Total]-(((([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)*(15/100))), 
CCur(([Estimate Items Subtotal]+[Labour Subtotal]-((([Estimate Items Subtotal]+[Labour Subtotal])*(([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)))+([Estimate Items VAT Total]+[Labour VAT Total]-(((([Retention Involved?]*[Retention Involved?])*[Estimate.Retention Amount])/100)*(15/100))));

[Pimped]

I sort of solved it, just took products part and made a seperate query where it was an outer join

Took the above and made another query but added labour and did an outer join

Then took the above and put in estimate table and voila, it worked. Now just replacing the potential nulls with Nz([xx],0) so that any nulls are handled and my forms work without "name?" or "error"

Would still like to see if there is a simpler solution from one single query.