criterion for a sum

criterion for a sum

Post by S2VuTml1T » Sun, 04 Jan 2009 13:53:00


I refer to this post
http://www.yqcomputer.com/


My problem is almost the same as his however i am still unable to get the
query i want.

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Query1.[lDate/TImeOfMax], Sum(SalesTransaction.stTotalAmount) AS
stTotalAmountOfSum
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
Query1.[lDate/TImeOfMax];

My SQL looks like this.
I want my sum to be =StaffID, >=lDate/TImeOfMax and =stPaymentMode
when i sum my total amount.

Hope someone can help me.
 
 
 

criterion for a sum

Post by John W. Vi » Sun, 04 Jan 2009 15:10:47

On Fri, 2 Jan 2009 20:53:00 -0800, KenNiuM < XXXX@XXXXX.COM >



Your query as written will pair every single record in the SalesTransaction
table with every single record in Query1, since you don't have any
relationship between them.

You don't say anything about the nature of the tables or the query; you say
you want criteria but your query doesn't have any criteria; your "I want my
sum" sentence doesn't make sense to me ("I want my sum to be =StaffID" means
that you want the sum to equal the value of staffID...!?)

Please clarify.
--

John W. Vinson [MVP]

 
 
 

criterion for a sum

Post by S2VuTml1T » Sun, 04 Jan 2009 17:02:01

To clarify things, i will give the scenario of my project.

A cashier logs in to an account and the cashier's log in time is stored in
the database under cashierLoginDetails.

The cashier will than make transactions.
This info would be recorded on the salesTransaction table, field here are
salesNo, StaffId, CashierNo, PaymentMode, TotalRevenue

What i wanted was to have a report of the TotalRevenue (from the time the
cashier logs in to the time the cashier prints the report) sorting by
different paymentMode. There would be many cashiers as well, so the query
must be able to sort out the different cashiers as well

This is what my query looks like now.
http://www.yqcomputer.com/

Hope my explanations are clear.
 
 
 

criterion for a sum

Post by John W. Vi » Mon, 05 Jan 2009 04:29:57

On Sat, 3 Jan 2009 00:02:01 -0800, KenNiuM < XXXX@XXXXX.COM >



Sorry, it's a waste of time for both of us to post jpgs of query designs.

The *real* query is pure text. Open the query, select View... SQL, and post
the SQL text here. It's worth study, SQL is a very powerful query language and
you can do anything in SQL that you can do in the grid - and more.
--

John W. Vinson [MVP]
 
 
 

criterion for a sum

Post by S2VuTml1T » Mon, 05 Jan 2009 04:43:00

Here is my SQL,

SELECT SalesTransaction.stPaymentMode, Query1.lStaffID,
Query1.[lDate/TImeOfMax], Sum(SalesTransaction.stTotalAmount) AS
stTotalAmountOfSum
FROM SalesTransaction, Query1
GROUP BY SalesTransaction.stPaymentMode, Query1.lStaffID,
Query1.[lDate/TImeOfMax];

And the scenario is above. Hope you can help me!