Grouping and Calculating then reporting

[MelissaBerrett]

I have queries that are designed as follows Account No, Desc, then the balace for 5 facilties for each individual account number. IE
Acct_NO Acct_Desc Facility1.balance facilty2.balance
11101 Cash 342.00 1563.00

Then I group together in other queries the account numbers to equal different catagories. IE account 70000 through 79999 is qryNursingExpenses.
Acct_NO Acct_Desc Facility1.balance facilty2.balance
70000 Cash 342.00 1563.00
70001 Cash 500.00 7546.00

What I need to do is total the colums for all the different queries and then take that total and put it on a report. In the end I will have a Profit/Loss Summary.
Any suggestions on how to total colums this way, or am I trying to make access act like excel? Should I total account numbers by facility on the report and not in queries? Thanks

 

[DanChard]

Hi.

I will assume that all of the queries you want to total have the same number of columns and that the data in each of those columns is of the same datatype. By this I mean that if one query looks like:

Acct_NO Acct_Desc Facility1.balance facilty2.balance
11101 Cash 342.00 1563.00

...then all of them do.

As long as this is true, you can union all the queries together like so:

select * from query1
union select * from query2
union select * from query3
union select * from query4;

(assuming of course that you have 4 queries called query1, query2 and so on)

If you want to see total across all the queries then you could expand this query as follows:

select sum(facility1.balance), sum(facility2.balance)
from (select * from query1
union select * from query2
union select * from query3
union select * from query4) as temp_query;

Hope this helps, please post again if you need any further guidance.

 

[MelissaBerrett]

I'm sorry if I was not clear, or misunderstanding you. I need a total line for each colums in each individual query, not total of all the queries and not one total line for the whole query. Sorry.

qryNursing
Acct_NO Acct_Desc Facility1.balance facilty2.balance
70000 Cash 342.00 1563.00
70001 Cash 500.00 7546.00
(Need total)
total 842.00 9109.00


qryActivities
Acct_NO Acct_Desc Facility1.balance facilty2.balance
80000 Cash 342.00 1563.00
80001 Cash 500.00 7546.00
(Need total)
total 842.00 9109.00

Then on a report.....
Facility1.balance facilty2.balance
Nursing 842.00 9109.00
Activites 842.00 9109.00

I hope that is more clear, I appreciate the input!

 

[MelissaBerrett]

To make it more simple. I want to know the sum of specifice account numbers added together?? Make sense?