criterion for a sum

[KenNiuM]

I refer to this post
http://www.microsoft.com/office/com...e6-8575-847a6abde5a7&cat=&lang=&cr=&sloc=&p=1


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.

 

[John W. Vinson]

I refer to this post
http://www.microsoft.com/office/com...e6-8575-847a6abde5a7&cat=&lang=&cr=&sloc=&p=1


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.

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.

 

[KenNiuM]

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://s106.photobucket.com/albums/m268/KenNiuM/?action=view&current=Untitled1.jpg

Hope my explanations are clear.

I refer to this post
http://www.microsoft.com/office/com...e6-8575-847a6abde5a7&cat=&lang=&cr=&sloc=&p=1


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.

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]

This is what my query looks like now.
http://s106.photobucket.com/albums/m268/KenNiuM/?action=view&current=Untitled1.jpg

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.

 

[KenNiuM]

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!