Query for name total.

[hmongie]

Hello all,

I again ran into something that I can't figure out.

I have a table:

Date
Time
FirstName
LastName
SSN
InAmount
OutAmount

I need a query to sum up the InAmount and OutAmount into one total based on the SSN. This query is placed into a form that is then placed onto another form. The form is to alert the user if the amount of the Inamount and Outamount of a unique SSN totals above $10,000.01 on the current date.

So for example if on 01/01/07 if SSN=111-11-1111 has an Inamount of $5,000.00 and an OutAmount of $5,000.01 thus totaling $10,000.01, then the person's name will appear on the form list. This will change/clear when the date is 01/02/07.

Any Ideas on how to accomplish this?

 

[boblarson]

Create a field in the query that has this:

OverAmt:IIf(Nz([InAmount],0) + Nz([OutAmount])>10000,"Over","Ok")

And then you can put in the criteria "Over" and it will display only those who are over.

 

[hmongie]

Hi Bob,

I'm not sure how to do this. I'd tried creating a field called OverAmt and place the code:
IIf(Nz([InAmount],0) + Nz([OutAmount])>10000,"Over","Ok")
into the criteria. But I get a prompt for the OverAmt. Any other ideas that might help?


My records are like this.

MyDate MyTime FirstName LastName SSN InAmount OutAmount

01/01/07 1:00 PM John Doe 111-11-1111 $1,000.00 $0
01/01/07 1:05 PM Jane Doe 222-22-2222 $500.00 $0
01/01/07 1:30 PM John Doe 111-11-1111 $6,000.00 $0
01/01/07 1:35 PM John Doe 111-11-1111 $0 $8,000.50
01/01/07 1:40 PM Jane Doe 222-22-2222 $0 $6,000.99


Results to display should only be John Doe.


by the way what does "Nz" stand for?

 

[boblarson]

It doesn't go in the criteria, it goes into the FIELD spot EXACTLY as I wrote it; the WHOLE thing.

 

[hmongie]

Sorry,

I was still editing the prior post and didn't see your post.


I got it to work like you had mentioned. but when I input 5,000 and then 5,001, it doesn't display the result.

It only displays the InAmount if it is over $10,000.

Any ideas.

 

[boblarson]

Sorry,

I was still editing the prior post and didn't see your post.


I got it to work like you had mentioned. but when I input 5,000 and then 5,001, it doesn't display the result.

It only displays the InAmount if it is over $10,000.

Any ideas.

The original specifications said that if the TOTAL between InAmount and OutAmount was over 10,000 then to select that person. That's what it should return.

Oh, and NZ is for treating nulls as you can't add nulls so using the NZ function changes nulls to zero so that you can add the fields, if say OutAmount was null.

 

[hmongie]

Any more ideas to help me out. It doesn't combine the InAmount and OutAmounts at all. It only returns the record if the (per) record is more than $10,000 in the InAmount field. I need something to total like records based on the SSN.

 

[boblarson]

So are you looking for a Total Amount, regardless of the date/time, but for each person?

 

[Jon K]

My records are like this.

MyDate MyTime FirstName LastName SSN InAmount OutAmount

01/01/07 1:00 PM John Doe 111-11-1111 $1,000.00 $0
01/01/07 1:05 PM Jane Doe 222-22-2222 $500.00 $0
01/01/07 1:30 PM John Doe 111-11-1111 $6,000.00 $0
01/01/07 1:35 PM John Doe 111-11-1111 $0 $8,000.50
01/01/07 1:40 PM Jane Doe 222-22-2222 $0 $6,000.99

Try this Totals Query, replacing with the correct table name:-

SELECT MyDate, FirstName, LastName, SSN, Sum(InAmount) AS SumOfInAmount,
Sum(OutAmount) AS SumOfOutAmount, SumOfInAmount+SumOfOutAmount AS Total
FROM [TableName]
GROUP BY MyDate, FirstName, LastName, SSN
HAVING Sum(InAmount)+Sum(OutAmount)>10000;
.

 

[hmongie]

JON!!!!!

Thanks so much. You are a genius!!! I forgot all about the SQL possibilities in Access.

It worked perfect with a few minor adjustments.

Thanks for taking the time to help me out. Wish I can be of service to you as well. Let me know. I owe you one.

BEST REGARDS...