lookup sum from another table

[BradCollins]

Is it possible to lookup the sum from another table ?

What I would like to do on my form is have a field that displays (and hopefully records) the sum from another table based on a matching record from the current record.
ie: In table1, if current record date is 03/03/06 then lookup (and sum) the values for fieldName in table2

Any help would be most appreciated, Thanks

 

[BoxHead]

Take a look at the DSum() function in Help.


HTH


John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[BradCollins]

I will do, I have tried SUM & also DLookup but neither worked for me, I will try this...Thanks

 

[BradCollins]

I tried the following :

=DSum("[fldORAmount]","tblORDetail",[fldORCategory]="Cash (over/under)" And frmWE!fldORDate=tblORDetail!fldORDate)

but it didnt work, all i get is #name as a response.

Any Ideas ?

 

[BradCollins]

It also doesnt seem to work if I restrict it to just one criteria
I have tried:

=DSum("[fldORAmount]","tblORDetail",[fldORCategory]="Cash (over/under)")

and still get the #name error

HELP!!!

 

[BoxHead]

Syntax issues:
The criteria needs to be enclosed in quotes, you need ampersands to concatenate the criteria string and you need to reference the control on the form.

I put the changes to try first in bold. I'm not sure what the red text refers to. Is that a field in the table or the name of a contrl on the form?

If it's a control on the form:

=DSum("[fldORAmount]","tblORDetail","[fldORCategory]=" & [Cash (over/under)] & " And [fldORDate] = #" & frmWE!fldORDate & "#")

If it's a value that would be found in the [fldORCategory]:

=DSum("[fldORAmount]","tblORDetail","[fldORCategory]= 'Cash (over/under)' And [fldORDate] = #" & frmWE!fldORDate & "#")

HTH


John

 

[BradCollins]

Sorry I should have provided more detail, What I am trying to achieve is the sume of all values held in [fldORAmount] in the table tblORDetail, but limit this calculation to the value 'Cash (Over/Under)' held in the field [fldORCategory] and only if the field [fldORDate] = [fldORDate] from the form frmWE

It looks like the last example you had would be the correct one, but again I only get the #Name error.

Thanks for your patience, I really appreciate this help

 

[BoxHead]

I'm assuming you are pasting these into the control source of an unbound textbox?

What happens if you use

=DSum("[fldORAmount]","tblORDetail")

Do you get the total of all fldORAmount or do you still get the #Name error?

Let me know.


John

 

[BoxHead]

Brad,

I gotta turn in. It's late here.

I'll check back in the morning. Let me know how the test worked out. The #Name error suggests that there's a syntax issue I believe.


JOhn

 

[BradCollins]

John,

Thanks for your help, I have put

=DSum("[fldORAmount]","tblORDetail")

into my textbox and I now get a result, the sum of all the fldORAmounts.

 

[BoxHead]

Hey, Brad. Sorry for the delay.

I built a table and form like yours and got the following to work,

=DSum("[fldORAmount]","tblORDetail","[fldORCategory]=  'Cash (over/under)'  And tblORDetail!fldORDate =  #" & [Forms]![frmWE]![fldordate] & "#")

Be sure to type in the equal sign.


HTH


John

 

[BradCollins]

Thanks again for your help, this as worked, but only if their is only one figure, once I have multiple figures it doesnt seem to work, it doesnt error either, just leaves the field blank.

 

[BradCollins]

Actually with further testing and playing around, I have determined that it will add up multiple amounts, but only displays those that result in a negative value
ie:
a: ($10) + ($5) result = 'blank'
b: (-$10) result = -$10
c: ($40) result = 'blank'
d: (-$10) + (-$15) result = -$25

both samples b and d are correct and display correctly, however examples a and c result in a blank field, that is to say do not display anything.
Obviously the code works, it just seems to be supressing any positive results

 

[BradCollins]

Well I dont know, the code works but there just seems to be a few random days where it will not display an answer regardless of the figures put in or the result.
It appears it doesnt matter what is selected or whether it is a negative or positive result, just random past days.

Because it is for mainly future use, I am not that concerned.

Once again thank you for your help, time & patience, I truely appreciate it.

Thank You

 

[BoxHead]

If no record satisfies the criteria argument or if domain contains no records, the DSum function returns a Null.

It might be worth checking the source data for a couple of things.

Is it possible that someone entered "Cash (over-under)" with a dash instead of a slash or omitted the space after the word 'cash'?

Any difference like that would not include the record in the DSum() value returned.

You can use a SELECT DISTINCT query for a quick check of the values in this field: SELECT DISTINCT tblORDetail.fldORCategory
FROM tblORDetail;


Further, take a look at the dates. Is it possible that someone was entering 2005 dates since the first of the year. If records were added or dates modified in January for November and December, they may actually be entered as Novemeber 2006 and December 2006. If someone types 11/14 into a date field on December 31st 2005, Access will interpret it as 11/14/2005. If they type that in on January 1st 2006, Access will assume it's 11/14/2006.

Also, with the dates, is it possible that someone is entering times along with the date?

Use the SELECT DISTINCT with the date field to get a quick look at what data is in the table.

If the issue you're having now is data entry inconsistency, there are update queries you can run to fix the existing data and some pretty simple (usually ) steps to take to ensure the integrity of the data at input.

HTH


John

 

[BradCollins]

Another star for you, thanks again, it was the date format that was restricting the data.

All is working great now...thanks again for all your help