Access and totals/sums query criteria

[rsanna]

I have a query that lists
User id, transcation id (the unique value), the date, and the amount that in turn generates a report.

I created a form to enter in an amount parameter, for example everything over $500. Is it possible to have access add up all the amounts for each user id and pull only those user ids that are greater than $500 in total.

Example:
115, 001, 1/1/16, $450
115, 002, 2/1/16, $60
198, 003, 2/1/16, $550
126, 004, 2/1/16, $400

So I would want 115 & 198 pulled but not 126. However, right now only 198 would get pulled with a simple greater than criteria. I would still want the individual transcations listed on the report.

[PhilOfWalton]

Your question is unclear. Are you saying that you want all users and their transactions where the total transaction is >= 500, or are you saying you want a report showing all users and transactions, and highlight those users & transactions where the total >= 500?

In either case, your starting point is

DSum("TransactionAmount","Transactions", "UserID = " & UserID)
 

I am assuming that UserID is a long number in a table called "Transactions"

Phil

[rsanna]

Sorry, yes, I want a query that will list all users and their transactions where the sum total of those transactions is greater than 500.

So if someone has a transaction of $450 and of $60 they will show up, instead of only those with single transactions over $500.

I put this in the criteria for the Transaction Amount, but it just gives me every entry (and using > instead gives me nothing).

500<DSum("TransactionAmount","Transactions","UserID = " & "UserID")

[PhilOfWalton]

Try this

SELECT UserID, TransactionAmount
FROM Transactions
WHERE ((DSum("TransactionAmount","Transactions","UserID = " & [UserID]))>=500)
ORDER BY UserID;
 

It may be worth bearing in mind that over the course of time, the total for any user is likely to increase until eventually the total will all be over $500 for most of your users.
You may need to think about additional restraints such as $500 in a month or $500 in a year

Phil

[rsanna]

I'm still getting an error code - Data type mismatch.

SELECT UserID, TransactionAmount
FROM Transactions
WHERE (((DSum("TransactionAmount","Transactions","UserID = " & [UserID]))>=500))
ORDER BY UserID;

I don't know what could be causing the mismatch, because a simple greater than works just fine in the TransactionAmount. The UserID is weird as it always 6 characters in length, but if it is say "3" then it is really " 3". My source material is also coming from a Union query if that makes any difference.

[PhilOfWalton]

I assumed UserID was a number. If it's Text which you are implying then the SQL needs inverted commas (double quotes) round the UserID.
I prefer to use Chr$(34) which is the ascii code for double quotes so the SQL becomes:'

SELECT UserID, TransactionAmount
FROM Transactions
WHERE ((DSum("TransactionAmount","Transactions","UserID = " & & Chr$(34) & [UserID] & Chr$(34)))>=500)
ORDER BY UserID;
 

Phil