Subtotal in a Query

[Gus]

Hi,

I have a designed query which includes the (relevant to
this question) columns "ARNumber", "ClientNo"
and "Revenue". Some of our clients may have multiple lines
of data where they will always have the one "ARNumber"
while the "ClientNo" changes to make each record
individual. I wish to have a subtotal of the "Revenue"
amounts for all identical "ARNumbers" to give a total
client value.

I would also like this information displayed in the query
giving the clients total "Revenue" at the end of each data
row.

Any help in the coding used for this would be great!

Thanks for your help

Gus

 

[Arvi Laanemets]

Hi

SELECT ARNumber, SUM(Revenue) AS SumRevenue FROM YourTable GROUP BY
ARNumber.

But in general, I'm afraid your database design isn't best. It's a bad
practice to have this kind of information all in one table. I don't know for
what is this AR in your database, but I suspect you can consider the design
(I assume that one ARNumber contains revenues for one specific client. The
is no limits principially, how much different rows will a client have in
table AR)

AR: ARNumber, ClientID, ... (with ARNumber as unique key index, and
ClientID as non-unique index, when the number of rows for client is not
limited, or unique, when every client can have only single row - but then
why dou you need the ARNumber at all.
)
AR_Rows: ID, ARNumber, ..., Revenue (with ID as unique key index - can be
autonumber, and ARNumber is non-unique index)

Set relation between AR and AR_Rows: all rows from AR and those from AR_Rows
where ARNumber is same sa in AR

Your query will be
SELECT a.ClientID, a.ARNumber, SUM(b.Revenue) AS SumRevenue FROM AR As a,
AR_Rows As b WHERE b.ARNumber=a.ARNumber GROUP BY a.Client, a.ARNumber
, and it returns summary revenues for every client grouped by ARNumber