Query to show four sums from 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

 

[randy700]

Try this.....

SELECT TOP 1 DSum("Qty","Table1") AS FirstOutput, DSum("Qty","Table2") AS SecondOutput, DSum("PricePaid","Table1") AS ThirdOutput FROM Table1, Table2

Substitute your field and table names in each DSum. Substitute you table names in the FROM clause. Add as many outputs as you need but make sure you include all tables used in the FROM clause.

 

[92722222]

Hi thanks Randy for the reply
I have tried what you said but i seem to get an error message

here is the code i am using

SELECT TOP 1 DSum("tender quantity","tenderbackup") AS FirstOutput, DSum("tender quantity","tenderlink") AS SecondOutput, DSum("quantity ordered","order") AS ThirdOutput, DSum("value of invoice","invoice") AS FourthOutput FROM tenderbackup, tenderlink, "order", invoice;

The "order" seems to come up with an error.

I have two fields which i need to calculate, quantity * unit price paid = total. i then need to do a report which can calculate all the total sum

Do you know if there is a way

many many thanks for help so far

kabir

 

[johnwm]

If your field names or table names have spaces in them, enclose the name in [ ]

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[randy700]

Try using brackets around the field names because of the spaces. Also, remove the "" from around order in your FROM clause....

SELECT TOP 1 DSum("[tender quantity]","tenderbackup") AS FirstOutput, DSum("[tender quantity]","tenderlink") AS SecondOutput, DSum("[quantity ordered]","order") AS ThirdOutput, DSum("[value of invoice]","invoice") AS FourthOutput FROM tenderbackup, tenderlink, order, invoice;

Where does quantity and unit price paid come into the picture?