One to Many Table Calculation

[NLR]

Hello,
I'm currently using a Dlookup function to display a calculated field from a query. This works fine when only one record is entered on the many side; however when additional records are entered it doesn't add the additional records. What function/code can I use to add each record's total as it's entered on the many side?
This is what is working when the many side only has one record:
=(DLookUp("[qryTotalBalance]![NetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))

I'm at a loss as to how to go about this...

Thank you for your help in advance.
NLR

 

[Gasman]

Dsum?


http://www.techonthenet.com/access/functions/domain/dsum.php

if only a few records, or a query if lots of the 'many' records. That would be more efficient I believe.

 

[NLR]

Hi Gasman,
Thank you for your quick reply.
I am trying the DSum...
Here is the code I have so far:
RecTotal: (DSum(" [TotalRecAmt]","qryTotalBalance",'[tblRecoveryAcctGLs]![RecAcctID]=[tblRelatedAccts]![AcctID]'))
However access does not recognize the criteria. I'm not sure how to write it. I need to sum all the many records of the one side in order to create another field that will give me the difference of the one side to the total of the many side. I hope this is not too confusing.
Thanks for your help.
NLR:banghead:

 

[Gasman]

I'm not even sure if you can use Dsum that way.

The syntax is like DSum("UnitPrice", "Order Details", "OrderID = 10248") according to that link. So you just tell it which field, the table/query, and the criteria.

You appear to referring to two extra tables as the criteria.
Why have you changed the criteria.? Try using your original criteria in the DSUM function

Should be along the lines of

=(Dsum("NetBalance","qryTotalBalance","[CustID]= " & Nz([CustID],0)))

 

[NLR]

Hi Gasman,
That worked!!! Yeah!!
Ok, so now I need to add code that will force the formatting the results to currency.

Would you be able to show me how to do this?

Thanks sooooo much for your help!!!
NLR

 

[Gasman]

You do that in the Format property of the control you are using. No code required.

 

[NLR]

Ok, that worked. Didn't see that...
Now I put these fields in a form using a Dlookup to display the results for the one side of each record; however it only displays the correct amount when the many side has only 1 record. As soon as it has more than one, the field is blank.
Here is my code:
=(DLookUp("[qryTotalBalance]![MNetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))

Do you have any ideas on this?
Thank you!!!
NLR

 

[Gasman]

I don't know what you are trying to achieve and have no idea as to the structure of your data.?

I can only view mdb databases. Can you upload a sample db in 2003 format?

 

[NLR]

Hi Gasman,
I only have access to Access 2010 and my db is quite large.
I created a query to provide 3 fields to use on a form. The first totals the one side amounts, the second (which you've helped me with) totals the many side amounts, the third provides the difference of the two.
I need to display these total fields on a form. The first field, TotalODAmt, displays fine. The second and third fields display ok as long as the many side has only 1 record. If a second or more records are added, the field is blank. I'm using DLookup to display the fields.
Second field code:
=DLookUp("[qryTotalBalance]![MTotalRecAmt] ","[qryTotalBalance]","CustID= " & Nz([CustID],0))
Third field code:
=(DLookUp("[qryTotalBalance]![MNetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))
Basically they are the same except point to the two different fields.
The query produces the correct totals. However if the many side has more than one record, the first record is blank and the second record has the correct total. Would this have something to do with it?

Thank you again for your help! It is much appreciated!
NLR

 

[Gasman]

Have you tried Dsum on the second field.? From what you describe you are looking for the sum of those values for the custid?

I wouldn't be be totalling the difference either. If you have both values, just subtract one from the other.?

Can you perhaps post the query sql code in a code window and a pick of the results?

 

[NLR]

SQL of my query:
SELECT tblOverdrawnAccts.CustID, tblRecoveryAcctGLs.AcctID, tblOverdrawnAccts.TotalODAmt, tblRecoveryAcctGLs.TotalRecAmt, [tblOverdrawnAccts]![TotalODAmt]-[tblRecoveryAcctGLs]![TotalRecAmt] AS NetBalance, (DSum(" [TotalRecAmt]","qryTotalBalance"," [tblOverdrawnAccts]![CustID] = " & Nz([tblRecoveryAcctGLs]![CustID],0))) AS MTotalRecAmt, [TotalODAmt]-[MTotalRecAmt] AS MNetBalance
FROM tblOverdrawnAccts INNER JOIN tblRecoveryAcctGLs ON tblOverdrawnAccts.AcctID = tblRecoveryAcctGLs.AcctID;

I've attached pictures of form that shows the fields correctly (only 1 record on the many side) and the field that shows incorrectly (more than 1 record).
I hope I gave you what you asked for.

NLR

 

[Gasman]

Sorry I'm lost now.

What is the source recordset for the form?
What is the source for those 3 fields.
Which table is the 1 and which table is the many.

Where does qryTotalBalance come into it.

Should we not be summing fields in the query with correct relationships.

I am finding it very hard to envisage your setup and how it is all working.

 

[NLR]

I'm sorry this is so confusing...

What is the source recordset for the form? tblOverdrawnAccts
What is the source for those 3 fields. These are calculated fields in the query, qryTotalBalance.
Which table is the 1 and which table is the many. tblOverdrawnAccounts is the 1 and tblRecoveryAcctGLs is the many.

Where does qryTotalBalance come into it. This query provides the fields for the DLookup on the form.

Should we not be summing fields in the query with correct relationships.
Calculated fields in the query:
NetBalance: [tblOverdrawnAccts]![TotalODAmt]-[tblRecoveryAcctGLs]![TotalRecAmt]

MTotalRecAmt: (DSum(" [TotalRecAmt]","qryTotalBalance"," [tblOverdrawnAccts]![CustID] = " & Nz([tblRecoveryAcctGLs]![CustID],0)))

MNetBalance: [TotalODAmt]-[MTotalRecAmt]

NLR

 

[Gasman]

And which of those fields are using that code I posted

=(Dsum("NetBalance","qryTotalBalance","[CustID]= " & Nz([CustID],0)))

So in qryTotalBalance we have multiple custid for the same customer.?

What is the name of the query where you posted the above code from.?

If the 3 fields are supplied by qryTotal Balance, why are we not using DSUM on the other fields.?

 

[NLR]

Hi Gasman, Sorry, I was pulled away for awhile...
I have a Customer table, CustID, which is a one to many relationship to table, tblOverdrawnAccts. Table tblOverdrawnAccts has a one to many relationship to table, tblRecoveryAcctGLs.
I created a calculated field in both tables, tblOverdrawnAccts and tblRecoveryAcctGLs to provide a total of all amounts. (I know this is probably a no no; but it seemed to help.)
The code you've helped me with is in the query, qryTotalBalance. I needed to change a field in it to:
MTotalRecAmt: (DSum(" [TotalRecAmt]","qryTotalBalance"," [tblOverdrawnAccts]![CustID] = " & Nz([tblRecoveryAcctGLs]![CustID],0)))

(The "M" for me stands for multiple records)
I only have one CustID per customer; however one customer can have more than one account and the one account can have more than one recovery. I've attached the relationship.
Thank you for your patience! I'm still very new at this.
NLR

 

[Gasman]

Ye me to. Sometimes it is worth taking a break and then starting afresh.

I actually work up this morning thinking about this problem.

From you previous post, you stated that the three fields are populated by the data in qryTotalBalance, so should we not be using DSUM and not DLOOKUP for the custid?

I have to go out for the morning, so will take a while to reply to any later posts.