help for union query

[confuse]

Please help me with the union query,,

It consist of 2 queries and 1 table. (Petshop orders,clinic orders and orders).
This query calculate the total sales, (petshop orders.total+clinic orders.totalcharges), this works fine. But if one of the field dont have value then no value will display. I use already the Nz function.

Here is my code in my union query:

SELECT [OrderID],[OrderDate],Nz([Order Details Petshop Query].Total,0) AS Petshop,Nz([Order Details Query Query].TotalCharges,0) AS Klinik,(Nz([Total],0) + Nz([TotalCharges],0)) AS Sales FROM [Orders]

UNION SELECT [OrderID],[OrderDate],Nz([Total],0) AS [Petshop],NULL,NULL FROM [Order Details Petshop Query]

UNION SELECT [OrderID],[OrderDate],NULL,Nz([TotalCharges],0) AS [Klinik],NULL FROM [Order Details Query Query];


Hope anyone can help me with this,,,Thanks...

 

[Rich]

How far ahead are you with this db? In reality you don't need seprate tables for sales, just another field in one table showing where the sale came from.

 

[stopher]

As Rich has said, your Petshop orders and Clinic orders should be in the same tables. They are after all just orders (albeit identified perhaps by a flag). Then the challenge of adding them up becomes much easier (as well as the fact you database will be better designed).

Also, I see you are trying to append totals. Why do you want to do this? Mixing detail and aggregate rows in a query is not a normal thing to do. If you want total rows in a report then reports are very good at producing totals (using grouping). No need to add the totals to the bottom of your query.

hth
Chris