Getting Grand Total from the Sum of Numbers in a different table

[Guest_imported]

I have a form called "Total Spent" and all that is on this form is the Grand Total. What I want to display in grand total is the sum of all purchases from a particular client. Let me explain how my database is set up


I have two tables [Client Info] that has the name etc... and I have [Client Purchases] which contains all purchase amounts of the client, and this is where I get the grand total from. The tables are joined by [Client ID] which is an autonumber. How do I get the grand total of a particular client using the above forms? I tried..

=sum(table![purch hist]![purch amt])

but it gives me an #Error. Please help!! Also The grand total must only be for that particular client, so as to not have the grand total of ALL purchases but instead just for that one client. Thanks

 

[HAH]

I would create a small loop to add everything to a Variable then write the value to the textbox for display.
For example, no the OnClick even of the Customer you want to see you do something like,

Function XXX
strSQL = "YOUR SQL STATEMET WHERE CLIENT_ID = " & RecordSetClone(ID)
Set MyDB = dbengine.workspaces(0).databases(0)
Set MyRS = MyDB.Openrecordset(strsql)
do while not MyRS.EOF
Total = MyRS("Amount&quot + Total
MyRS.MoveNext
loop

txtName = Total
end function

 

[SBendBuckeye]

Try the aggregate function DSUM as follows:

txtGrandTotal = DSUM("[purch amt]", "[purch hist]", _
"[Client ID] = " & txtClientID)

The first field is the field upon which you want to operate in double quotes, the second field is the table name containing the first field also in double quotes, and the third field is the SQL WHERE clause without the WHERE part. The last part is a field from your form which contains the Client ID you want.

NOTE: If you do this type of logic on a form to get total invoice amount for a detail screen, it will not update until the record updates.

Good Luck!

 

[Guest_imported]

This is what I entered, why si this still wrong?

I used the expression creator and it asked for a domain but i didnt' know what it would be so I just deleted it out, what is the domain by the way?

=DSum([Purch Hist]![Purch Amt],[Forms]![Client Info]![Client ID])

 

[SBendBuckeye]

Sorry I wasn't clear. My example was trying to use your tables. The DSUM format is

DSUM = ("[Sum Field]", "[Sum Field Table]", _
"Criteria = " & Field Name

You don't need to qualify anything. Sum Field is the field name in the table you want to accumulate for a grand total.
Domain is just the name of the table or query you want to accumulate a sum against.

Using your example from above:

=DSum("[Purch Amt]","[Table that contains Purch Amt]", _
"[Client ID] = " & [Control Name on form that holds
the Client ID (eg txtClientID])

Good Luck!

 

[Guest_imported]

For some reason it still isn't working It doesn't like the expression...


=dsum("[Purch Amt]", "[Purch Hist]","[Client ID] = " & [Client ID])


is the _ just where the new line begins or do I need to have that in there too? At this point i have ommitted it, thanks

 

[SBendBuckeye]

The _ is just a line continuation character. The last variable after the & should be the control name from your form that contains the Client ID. If it has spaces in it, try renaming it to have no spaces and get rid of the [] around it (eg last part: ,"[Client ID] = " & txtClientID) where txtClientID is the name of the control on your form.

Good Luck!

 

[SBendBuckeye]

Post your email and I'll try to send you a little piece of working code this weekend

 

[Guest_imported]

e-mail: calquik911@AOL.COM

Thanks

 

[SBendBuckeye]

Check your email.

Good Luck!