Group By Sum of Transaction Amounts

[Guest]

Table A contains the following fields

Transaction Date
Account Number
Location
Transaction Amount (mostly a credit amount)
User ID
Mgr Id

Multiple Transaction amounts can be made to the account either by 1 user or
multiple users.

On one account, I processed $5000 credit and on the same account you
processed $2000 credit, all done during a month one period 11/1-11/30. My
query is Group by Sum of Transaction Amount where it is < - 999.99.

When the query is run, I don't get any results back because 2 different
user's processed an adjustment.

If the same person processed the $7000 credit, this account is showing up in
the results.

Regardless of how many people issued the credit, I want any credits that
total greater than or equal to -999.99 for each account and it is now coming
up.

 

[Guest]

I removed the User ID and the Group BY for this criteria and I still come out
with the same results.

Here is my SQL....

SELECT TableA.Account, TableA.[Transaction Date], Sum(TableA.[Transaction
Amount]) AS [SumOfTransaction Amount], TableA.Market, TableA.[Customer Name],
TableA.[CSR Name], TableA.[MGR Name]
FROM TableA
GROUP BY TableA.Account, TableA.[Transaction Date], TableA.Market,
TableA.[Customer Name], TableA.[CSR Name], TableA.[MGR Name]
HAVING (((TableA.[Transaction Date]) Between [start date] And [end date])
AND ((Sum(TableA.[Transaction Amount]))<-999.99));

Dear ljp:

I take it that [User Id] is in your GROUP BY clause.

Now, if you want to show the sum of the Transaction Amount column across all
values of [User Id], then the query cannot tell you WHICH user performed the
transactions, especially since, in principle at least, there could be
several separate users involved. It only makes sense, then, that you cannot
see which user entered the transaction(s) being summed if they are to be
summed across ALL users.

Remove [User Id] from the SELECT clause and from the GROUP BY clause and it
should work. Make sense?

Perhaps I haven't understood the problem correctly. If so, please post your
query SQL text. I would then try again, if I can.

Tom Ellison

Table A contains the following fields

Transaction Date
Account Number
Location
Transaction Amount (mostly a credit amount)
User ID
Mgr Id

Multiple Transaction amounts can be made to the account either by 1 user
or
multiple users.

On one account, I processed $5000 credit and on the same account you
processed $2000 credit, all done during a month one period 11/1-11/30. My
query is Group by Sum of Transaction Amount where it is < - 999.99.

When the query is run, I don't get any results back because 2 different
user's processed an adjustment.

If the same person processed the $7000 credit, this account is showing up
in
the results.

Regardless of how many people issued the credit, I want any credits that
total greater than or equal to -999.99 for each account and it is now
coming
up.

 

[Tom Ellison]

Dear ljp:

It really helps to see the query.

If the show the Transaction Date and group by it, then it will NOT SUM the
Transaction Amount across multiple dates. Same principle. To show each
date on which a transaction occurs, it must break out each and avery date.

Start more simply. Try this:

SELECT Account,
SUM([Transaction Amount] AS SumOfTrnsactionAmount
FROM TableA
WHERE [Transaction Date] BETWEEN [start date] and [end date]
GROUP BY Account
HAVING SUM([Transaction Amount]) < 999.99

Note: do you mean < 1000, meaning to show when the sum is 999.99? Or do
you really mean the maximum sum to be 999.98?

Refering to the above query, does it run? Does it produce something like
the correct results? What other information do you want from this query?

I don't like this thing where criteria that belong in a WHERE clause end up
in the HAVING. Anybody else notice this, or object to it? Not your fault,
ljp.

Tom Ellison

I removed the User ID and the Group BY for this criteria and I still come
out
with the same results.

Here is my SQL....

SELECT TableA.Account, TableA.[Transaction Date], Sum(TableA.[Transaction
Amount]) AS [SumOfTransaction Amount], TableA.Market, TableA.[Customer
Name],
TableA.[CSR Name], TableA.[MGR Name]
FROM TableA
GROUP BY TableA.Account, TableA.[Transaction Date], TableA.Market,
TableA.[Customer Name], TableA.[CSR Name], TableA.[MGR Name]
HAVING (((TableA.[Transaction Date]) Between [start date] And [end date])
AND ((Sum(TableA.[Transaction Amount]))<-999.99));

Dear ljp:

I take it that [User Id] is in your GROUP BY clause.

Now, if you want to show the sum of the Transaction Amount column across
all
values of [User Id], then the query cannot tell you WHICH user performed
the
transactions, especially since, in principle at least, there could be
several separate users involved. It only makes sense, then, that you
cannot
see which user entered the transaction(s) being summed if they are to be
summed across ALL users.

Remove [User Id] from the SELECT clause and from the GROUP BY clause and
it
should work. Make sense?

Perhaps I haven't understood the problem correctly. If so, please post
your
query SQL text. I would then try again, if I can.

Tom Ellison

Table A contains the following fields

Transaction Date
Account Number
Location
Transaction Amount (mostly a credit amount)
User ID
Mgr Id

Multiple Transaction amounts can be made to the account either by 1
user
or
multiple users.

On one account, I processed $5000 credit and on the same account you
processed $2000 credit, all done during a month one period 11/1-11/30.
My
query is Group by Sum of Transaction Amount where it is < - 999.99.

When the query is run, I don't get any results back because 2 different
user's processed an adjustment.

If the same person processed the $7000 credit, this account is showing
up
in
the results.

Regardless of how many people issued the credit, I want any credits
that
total greater than or equal to -999.99 for each account and it is now
coming
up.

 

[Guest]

In your note below: < 1000 would be (greater than or equal to a credit of
999.99)

I will try the query and let you know. As far as your explanation it appears
to be what I am looking for.

One other piece of information that I would like, but it doesn't seem
possible is that I would need to have at least one user id per result. Maybe
the user id of who had the greater credit amount.

Dear ljp:

It really helps to see the query.

If the show the Transaction Date and group by it, then it will NOT SUM the
Transaction Amount across multiple dates. Same principle. To show each
date on which a transaction occurs, it must break out each and avery date.

Start more simply. Try this:

SELECT Account,
SUM([Transaction Amount] AS SumOfTrnsactionAmount
FROM TableA
WHERE [Transaction Date] BETWEEN [start date] and [end date]
GROUP BY Account
HAVING SUM([Transaction Amount]) < 999.99

Note: do you mean < 1000, meaning to show when the sum is 999.99? Or do
you really mean the maximum sum to be 999.98?

Refering to the above query, does it run? Does it produce something like
the correct results? What other information do you want from this query?

I don't like this thing where criteria that belong in a WHERE clause end up
in the HAVING. Anybody else notice this, or object to it? Not your fault,
ljp.

Tom Ellison

I removed the User ID and the Group BY for this criteria and I still come
out
with the same results.

Here is my SQL....

SELECT TableA.Account, TableA.[Transaction Date], Sum(TableA.[Transaction
Amount]) AS [SumOfTransaction Amount], TableA.Market, TableA.[Customer
Name],
TableA.[CSR Name], TableA.[MGR Name]
FROM TableA
GROUP BY TableA.Account, TableA.[Transaction Date], TableA.Market,
TableA.[Customer Name], TableA.[CSR Name], TableA.[MGR Name]
HAVING (((TableA.[Transaction Date]) Between [start date] And [end date])
AND ((Sum(TableA.[Transaction Amount]))<-999.99));

Dear ljp:

I take it that [User Id] is in your GROUP BY clause.

Now, if you want to show the sum of the Transaction Amount column across
all
values of [User Id], then the query cannot tell you WHICH user performed
the
transactions, especially since, in principle at least, there could be
several separate users involved. It only makes sense, then, that you
cannot
see which user entered the transaction(s) being summed if they are to be
summed across ALL users.

Remove [User Id] from the SELECT clause and from the GROUP BY clause and
it
should work. Make sense?

Perhaps I haven't understood the problem correctly. If so, please post
your
query SQL text. I would then try again, if I can.

Tom Ellison


Table A contains the following fields

Transaction Date
Account Number
Location
Transaction Amount (mostly a credit amount)
User ID
Mgr Id

Multiple Transaction amounts can be made to the account either by 1
user
or
multiple users.

On one account, I processed $5000 credit and on the same account you
processed $2000 credit, all done during a month one period 11/1-11/30.
My
query is Group by Sum of Transaction Amount where it is < - 999.99.

When the query is run, I don't get any results back because 2 different
user's processed an adjustment.

If the same person processed the $7000 credit, this account is showing
up
in
the results.

Regardless of how many people issued the credit, I want any credits
that
total greater than or equal to -999.99 for each account and it is now
coming
up.

 

[Tom Ellison]

Dear ljp:

For that last change, I recommend you create a separate query that gives the
"user id of who had the greater credit amount" and then combine the two
queries, showing this whenever the COUNT() of the first query is zero.

Tom Ellison

In your note below: < 1000 would be (greater than or equal to a credit of
999.99)

I will try the query and let you know. As far as your explanation it
appears
to be what I am looking for.

One other piece of information that I would like, but it doesn't seem
possible is that I would need to have at least one user id per result.
Maybe
the user id of who had the greater credit amount.

Dear ljp:

It really helps to see the query.

If the show the Transaction Date and group by it, then it will NOT SUM
the
Transaction Amount across multiple dates. Same principle. To show each
date on which a transaction occurs, it must break out each and avery
date.

Start more simply. Try this:

SELECT Account,
SUM([Transaction Amount] AS SumOfTrnsactionAmount
FROM TableA
WHERE [Transaction Date] BETWEEN [start date] and [end date]
GROUP BY Account
HAVING SUM([Transaction Amount]) < 999.99

Note: do you mean < 1000, meaning to show when the sum is 999.99? Or do
you really mean the maximum sum to be 999.98?

Refering to the above query, does it run? Does it produce something like
the correct results? What other information do you want from this query?

I don't like this thing where criteria that belong in a WHERE clause end
up
in the HAVING. Anybody else notice this, or object to it? Not your
fault,
ljp.

Tom Ellison

I removed the User ID and the Group BY for this criteria and I still
come
out
with the same results.

Here is my SQL....

SELECT TableA.Account, TableA.[Transaction Date],
Sum(TableA.[Transaction
Amount]) AS [SumOfTransaction Amount], TableA.Market, TableA.[Customer
Name],
TableA.[CSR Name], TableA.[MGR Name]
FROM TableA
GROUP BY TableA.Account, TableA.[Transaction Date], TableA.Market,
TableA.[Customer Name], TableA.[CSR Name], TableA.[MGR Name]
HAVING (((TableA.[Transaction Date]) Between [start date] And [end
date])
AND ((Sum(TableA.[Transaction Amount]))<-999.99));



:

Dear ljp:

I take it that [User Id] is in your GROUP BY clause.

Now, if you want to show the sum of the Transaction Amount column
across
all
values of [User Id], then the query cannot tell you WHICH user
performed
the
transactions, especially since, in principle at least, there could be
several separate users involved. It only makes sense, then, that you
cannot
see which user entered the transaction(s) being summed if they are to
be
summed across ALL users.

Remove [User Id] from the SELECT clause and from the GROUP BY clause
and
it
should work. Make sense?

Perhaps I haven't understood the problem correctly. If so, please
post
your
query SQL text. I would then try again, if I can.

Tom Ellison


Table A contains the following fields

Transaction Date
Account Number
Location
Transaction Amount (mostly a credit amount)
User ID
Mgr Id

Multiple Transaction amounts can be made to the account either by 1
user
or
multiple users.

On one account, I processed $5000 credit and on the same account you
processed $2000 credit, all done during a month one period
11/1-11/30.
My
query is Group by Sum of Transaction Amount where it is < - 999.99.

When the query is run, I don't get any results back because 2
different
user's processed an adjustment.

If the same person processed the $7000 credit, this account is
showing
up
in
the results.

Regardless of how many people issued the credit, I want any credits
that
total greater than or equal to -999.99 for each account and it is
now
coming
up.