I thought SUM() only had 1 output?

[cheuschober]

Hi -- So, I'm a bit new here to the Access world. I was previously using an inflexible excel spreadsheet to do act as a record for my various finances but have made the switch to access for a much more powerful interface. Unfortunately, though, with nothing more than a poor book and the Microsoft Help Interface I'm left with many holes in my understanding of this system.

My current problem resides in a query I have created from my expense logs table.

My [Expense Logs] table has all of my expenses logged with respective date, amount, expense code (be that grocery, gasoline, internet access, etc), payment code (cash, credit, cheque, etc) and extraneous payee and note information.

Each respective payment code has it's own query (NameTrans) defined for reports I've designed like a virtual chequebook, virtual credit statements, etc. These queries get somewhat complicated when you account the fact that I might use a cheque to pay off a credit card which is information entered with a payment code for CHEQUE but an expense code of CREDIT (one record's amount being subtracted from one query and added to another's)

For my grand totals category I need to separate each payment code's totals into a separate field.

As it stands, I've gotten myself via subqueries to obtain the correct totals but for some reason I am recieving 71 records of the same totals; one for each existing record in the table! Each field has it's correct total but I shouldn't be recieving 71, or so I thought!

My goal is for a query with just one record--each record containing my one total for each respective field's account. Current sql is found below:

SELECT (SELECT Sum([BankTrans].Deposit) FROM [BankTrans])-(SELECT Sum([BankTrans].Withdrawl) FROM [BankTrans]) AS Banking,

(SELECT Sum([CreditCardTrans].Amount) FROM [CreditCardTrans]) AS CreditCard,

(SELECT Sum([CashTrans].Amount) FROM [CashTrans]) AS Cash

FROM [Expense Logs]


If anyone has any ideas on how to clean this up I'd really appreciate it. I guess the whole subquery thing is one of those holes in my undestanding of this powerful program.

Thank you so much!
~Chad

 

[Jon K]

You have included in the query 71 records from the [Expense Logs] table (although you haven't selected any fields from that table in the query.) To limit the number of records returned to 1 only, you can use Top 1 or Distinct in the query i.e.

SELECT Top 1 (SELECT Sum(.......

SELECT Distinct (SELECT Sum(.......


Note
You can simplify Banking by combining the two Sums in one subquery:-

SELECT TOP 1 (SELECT Sum([BankTrans].Deposit) - Sum([BankTrans].Withdrawl) FROM BankTrans) AS Banking,..........


Since the [Expense Logs] table is not related in any way to the subqueries in the query, the normal way to do it is to create three independent Totals queries and then put them in a fourth query.
.

 

[cheuschober]

Thanks...

Wow, it's funny--I originally had this set up with three distinctive queries combined in a 4th and then read something that I interpreted to mean I had gone down the wrong path (hence the many select statements).

There are actually 6 different fields to be combined.

If there are, say, a planned 10 years worth of fields to be placed in the [Expense Logs] table at a rate of about 40-50 records per month what is going to be the most processor efficient way of going about obtaining these totals?

A SELECT DISTINCT statement or creating 6 individual queries and then combining them?

Thank you so much for the help,
~Chad

 

[Jon K]

I think combining 6 independent queries in a 7th query is more efficient because Access doesn't have to open the [Expense Logs] table.
.