Query to calculate total of four tables

[92722222]

Hi i am trying to do a query where i join four unrelated tables togeather in order to get the total value on each table. Each table contains data such as quantity, price paid, this is calcualted in the query but i cant seem to be able to get a total of the all the records

I need to some how show all of this on one report. I was told that i can do seperate queries then join them togeather but this has not been succesfull.

Does anyone have any suggestions


At present the tables show all the records i want. Is there any way where you cam make the query just show the total sum not all the records.

many thanks

 

[fkegley]

You should be able to compose a query that will total the individually calculated values. Here is a sample select statement that I used. You would then need to do queries for each of the other table's calculated fields. A UNION SELECT among the four queries should do it. Note that you will need to have the SAME NUMBER of fields n each SELECT statement.

SELECT Sum([UnitPrice]*[Quantity]) AS Total
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
UNION SELECT Sum([UnitPrice]*[Quantity]) AS Total
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
UNION SELECT Sum([UnitPrice]*[Quantity]) AS Total
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
UNION SELECT Sum([UnitPrice]*[Quantity]) AS Total
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID



Frank kegley
fkegley@hotmail.com