MagpiesNewsstand
Technical User
In running a query to show all sales from a single vendor, I have been unable to then SUM all of the quantites sold, as well as SUM the total prices.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to SUM a column in single table query 1
- Thread starter MagpiesNewsstand
- Start date
- Status
-
1
- #2
TonyMcGuire
Programmer
Something like this?
If not, just a *little* help with your situation would help us help you.
Tony McGuire
"It's not about having enough time. It's about priorities.
Code:
tablename.db | vendors | Quantity |
| mickysoft | calc sum as nbr |
tablename.db | Price |
| calc sum as vol |If not, just a *little* help with your situation would help us help you.
Tony McGuire
"It's not about having enough time. It's about priorities.
- Thread starter
- #3
MagpiesNewsstand
Technical User
Tony, Thanks for the speedy response.
The query I have set up on Tranhist.db results in an answer table (26 pages)with Date(>=1/1/06), Trans#, Item, Qty, Price, and Total Price (on that line only).
What I am hoping for is a new line at the bottom(?), top(?) indicating the total Qty, and the sum of all the total prices.
Do I modify this initial query with the "calc sum as"?,
or do I run a new query on the answer table? (if that is even possible).
or???
Thanks again,
Tom
The query I have set up on Tranhist.db results in an answer table (26 pages)with Date(>=1/1/06), Trans#, Item, Qty, Price, and Total Price (on that line only).
What I am hoping for is a new line at the bottom(?), top(?) indicating the total Qty, and the sum of all the total prices.
Do I modify this initial query with the "calc sum as"?,
or do I run a new query on the answer table? (if that is even possible).
or???
Thanks again,
Tom
TonyMcGuire
Programmer
Is this for a report?
You can't have the query generate an extra record with the extra info.
You need to do another query. Make the first one return the 'answer' as something other than 'answer.db'
Then query that table to a new table.
However, if this is for a report you don't even need to do that.
Just use a calculated field, with sum of quantity and sum of total price (I'm guessing on which field names you need to sum).
Tony McGuire
"It's not about having enough time. It's about priorities.
You can't have the query generate an extra record with the extra info.
You need to do another query. Make the first one return the 'answer' as something other than 'answer.db'
Then query that table to a new table.
However, if this is for a report you don't even need to do that.
Just use a calculated field, with sum of quantity and sum of total price (I'm guessing on which field names you need to sum).
Tony McGuire
"It's not about having enough time. It's about priorities.
- Thread starter
- #5
MagpiesNewsstand
Technical User
As a very interested novice at this, please excuse my need to ask for some fairly elementary hand holding.
In regard to your first suggestion:
How do I instruct a query to return the answer as something other than answer.db?
As for the second:
Is the calculated field part of a query?, or a report?
Do I run a query and then run a report (File|New|Report)on the answer table?
I guess what I'm really shooting for IS a "report". While I have played with extracting information with queries, I have not built any reports. In attempting it now I am not seeing answer.db as an available table to run a report from, nor any means of the report filtering the Tranhist.db and or calculating any fields.
Sorry for all the questions, your help is appreciated.
Apart from the help files, are there any other tutorials available that you might recommend?
In regard to your first suggestion:
How do I instruct a query to return the answer as something other than answer.db?
As for the second:
Is the calculated field part of a query?, or a report?
Do I run a query and then run a report (File|New|Report)on the answer table?
I guess what I'm really shooting for IS a "report". While I have played with extracting information with queries, I have not built any reports. In attempting it now I am not seeing answer.db as an available table to run a report from, nor any means of the report filtering the Tranhist.db and or calculating any fields.
Sorry for all the questions, your help is appreciated.
Apart from the help files, are there any other tutorials available that you might recommend?
TonyMcGuire
Programmer
Are you opening/creating a new query? Or running a script or somesuch, with a query in it?
Sounds like the first.
'answer.db' will be in the
riv: directory, unless you direct it elsewhere.
With the query open and a table chosen, and at least one field checked or a calc statement in a field,
go to Query | Properties
and you'll see where/what the answer table will be. Change it however you wish.
The calculated field I'm talking about is part of the report.
I'm not real good at the details. You should check into the news groups on - there are many there who can help with what you are looking for, I think.
Tony McGuire
"It's not about having enough time. It's about priorities.
Sounds like the first.
'answer.db' will be in the
riv: directory, unless you direct it elsewhere.With the query open and a table chosen, and at least one field checked or a calc statement in a field,
go to Query | Properties
and you'll see where/what the answer table will be. Change it however you wish.
The calculated field I'm talking about is part of the report.
I'm not real good at the details. You should check into the news groups on - there are many there who can help with what you are looking for, I think.
Tony McGuire
"It's not about having enough time. It's about priorities.
- Status
Similar threads
- Locked
- Helpful tip
- Locked
- Question
- Locked
- Question