Summing records with a common field

[Guest]

I ran a query with the following results:

Customer PO Price Handling Freight Tax
111 10 3 2
111 8 2 1 2
112 15 5 4
112 22 6 5
112 8 1 2 7
115 9 2 1
115 13 3 3 4


In a new column, I would like to calculate the sum of the "Price",
"Handling", and "Freight" fields for each group of records having the same PO
number. Is this possible?

Next, I'd like to compute the ratio of tax to the total I just calculated.
(Tax/(Price+Handling+Freight)

 

[Tom Ellison]

Dear MV:

I'm guessing you want a "running sum" something like this:

PO SPrice SHandling SFreight
111 10 3 2
111 18 5 3
112 15 5 4
112 37 11 9
112 45 12 11
115 9 2 1
115 22 5 4

There is a big problem in doing this. The rows of your data are not
uniquely sorted. When it comes time to look at the data and add up the
price on the "current line" with the price on all the "previous lines", how
can we know which lines are previous to others? How can we tell the order
is:

111 10 3 2
111 8 2 1 2

and not:

111 8 2 1 2
111 10 3 2

If you cannot sort the data uniquely, then the rows within each set for a PO
are not ordered at all. They will not necessarily appear in any consistent
order, because they are not uniquely ordered.

If you have a column that adds order to the rows within a set, then this can
be done in a query. Just tell me what column that is.

There is a mathematical term for this, it is a "bag". The jelly beans in
each bag (each different PO value) are being drawn out in no particular
order. This order will seem systematic for a while, but if rows are added
and deleted from the table (even form a different PO value) this order can
change.

I've made too many guesses already. Please just let me know if I'm on a
track that is anywhere close to what you need.

Tom Ellison

 

[Guest]

Your method sounds all right. I could get where I want to go with this
method, so I'll go with it. However, I'd have to get rid of the
"non-totaled" lines; I only want to see the totaled lines. For example, from
the original table, my query should only display three lines that look like
this:

PO Price Handling Freight Tax
111 18 5 3 2
112 45 12 11 7
115 22 5 4 4

I would need to delete all of the other lines to see this on the report.

I'm not sure if there's a unique identifier/key for each field. The PO
would be the key if there were one in this table, but it's a key for two or
three records rather than just one record.

Does this help you a little in seeing what I'm after?

 

[Tom Ellison]

Dear MV:

So, you would have:

SELECT [Customer PO] AS PO, SUM(Price) AS SPrice,
SUM(Handling) AS SHandling, SUM(Freight) AS SFreight,
SUM(tax) AS STax
FROM YourTable
GROUP BY [Customer PO]

I completely blew part of what I posted before, confusion the answer with a
prior persons question I had worked on. Must have been getting tired. I
had a good nap, so hopefully I'm doing better now.

Please disregard some of that garbled stuff! It must have been confusing!
Only I can understand it, because I remember how I was misunderstanding your
post.

Tom Ellison

 

[Guest]

I'm having trouble entering the command you showed me. I'm not sure where it
goes in the query design view. I try to put it in the "Criteria" row, but I
keep getting error messages.

Any advice?

 

[Tom Ellison]

Dear MV:

It doesn't go in the query design view. It is the SQL View, in its
entirety. This is the text of a complete, finished query.

When you get to the design view of a new query, don't select any table. SQL
will show up in the symbols at the top of the screen. Click and paste this
in. Substitute the actual name of your table.

Tom Ellison