Subtracting a total in a subform from a Main form calculated field.

My problem is I need to subtract the TotalAmountRecoup (Subform)from the TotalServAmountPaid (Main form), but it is only giving me the TotalServAmountPaid, which is what is calculated from the Main Form.

I have a situation where I have four (4) TABLES and only two of them have a unique field.  

Main form uses TABLE 1 and TABLE 2

Table 1 Fields
IDNo  = Keyfield
ReferName

Table 2 Fields
IDNo
DateOfServ
AcctName
ServCost
TransNo
ServAmountPaid

TABLE 1 is linked to TABLE 2 by IDNo (keyfield)
There are many different IDNo for a ReferName, and ReferName is not a unique field in TABLE 1


The Subform Property Sheet (frmRecoup) = Link Master Fields by TransNo from TABLE 2,
Link Child Field TransNo from TABLE 3.

The Subform uses TABLE 3, and TABLE 4 to link the TransNo to each other, TABLE 3

Table 3 Fields
TransNo – Keyfield
SumOfTransNo

Table 4 Fields
TransNo
TransNoRecoupFor
TransOverPaidAmount
ServDateForOverPaidTrans
AmtRecoup
IDNo

My problem is that the Main form which is linked to TABLE 1, by IDNo has many IDNo for a ReferName.  

I created a calculated field in the Main form to calculate the total ServAmountPaid  by TransNo from TABLE 2, and I created calculated field in the footer of the Subform to calculate the total AmtRecoup.  I named the calculated field TotalAmountRecoup, and named the calculated field on the Main form TotalServAmountPaid.
Delores_CAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Then add the PlanType to the both queries (and Group on the PlanType) and add the PlanType to the DLookup filter. You'll need to include the PlanType field ONLY on your report (you an add it as a Textbox, and set it Hidden so it won't show). Name the textbox "txPlanType", then change your DLookup to this:

=DLookUp("SumofSumofAmtApplied","q_TotalOfSumByProvID","ProviderName='" & [ProviderName] & "' AND PlanType='" & [txPlanType] & "'")
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could use DSUM for this:

= Nz(DSUM("ServAmountPaid", "Table2", "IDNo=" & Me.IDNo),0) - Nz(DSUM("AmtRecoup", "Table4", "IDno=" & Me.IDNo),0)

You could do this in the ControlSource of your control, placed on the MainForm. Of course, you'll have to modify the DSUMs to provide you with the correct information.
0
 
Delores_CAuthor Commented:
I am still getting an error " the expression you entered contained invalid syntax" (see what I am entering below)

= Nz(DSUM("ServAmountPaid", "tblFundsImport", "IDNo=" & Me.IDNo),0) - Nz(DSUM("AmtRecoup", "tblRecoup", "IDNo=" & Me.IDNo),0)

The name of the tables are tblRecoup and tluFundsImport.  I have created a sum for each table in the report footer of each of these tables.  Table tluFundsImport = ServAmountPaid   and tblRecoup = AmtRecoup.  


0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
"I have created a sum for each table in the report footer of each of these tables."

You referred to a Form in your question. Are you doing this in a Report? If you are, then do you have a control named "IDNo" on the report? Reports are a bit different - they require that the field be physically present on the report in order to refer to them.

Also, is "IDNo" a Text field or a Numeric field? If it's Text, then you'll have to enclose it in quotes:

= Nz(DSUM("ServAmountPaid", "tblFundsImport", "IDNo='" & Me.IDNo & "'"),0) - Nz(DSUM("AmtRecoup", "tblRecoup", "IDNo='" & Me.IDNo & "'"),0)



0
 
Delores_CAuthor Commented:
Sorry it is a report.

I copied your script, and each time I paste it in a text box on my report, it is putting in brackets, the Me.IDNo, can you tell me why this is happening?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access always puts brackets around these values.

Do you have a CONTROL (i.e. a textbox, or combo) on your Report that has the IDNo field as it's ControlSource? As stated above, this is required when working with reports. It's not enough to simply include tht field in the RecordSource of your report - you must have it on the report itself. It can be hidden, but it must be there.
0
 
Delores_CAuthor Commented:
It is only giving the last amounts in the results.  I need the sum of all amounts for this group.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, I don't know what you mean by "last amounts".

This statement:

DSUM("ServAmountPaid", "tblFundsImport", "IDNo='" & Me.IDNo & "'")

Will give you the SUM Of all values in the ServAmountPaid field in tblFundsImport where the IDNo field matches whatever value is found in Me.IDNo

This statement:

DSUM("AmtRecoup", "tblRecoup", "IDNo='" & Me.IDNo & "'")

Will give you SUM Of all values found in the AmtRecoup field in tblRecoup where the IDno field matches whatever value is found in Me.IDNo

If you need to further narrow down those scopes, then you can add to the WHERE portion of the DSUM statements (where the "IDNO=" stuff is). Those are built just like a standard SQL Where clause. If you can provide more information as to what you mean by "sum all amounts for this group" we might be able to help further.


0
 
Delores_CAuthor Commented:
I am trying to get together a copy of the database without confidential information contained to send so it can be seen and evaluated.  I am sure it had something to do with not having keyfields in the two tables that I am trying to calculate the information from.  
0
 
Delores_CAuthor Commented:
The name of the Report is rptMasterReport
The name of the SubReport is tblRecoup,


The field on the SubReport that I need to calculate is named txtApplied

The field to calculate on the MainReport is named TotalPaid

The Mainform is linked to a table named tblProviderAccount

The table that makeup the MainReport named is tluFundsImport, this table is linked to the tblProviderAccount table by ProvID, and has a field called Reference.  The Reference field links all the ProvID that has the same Reference name.

The tables that makeup the SubReport is named tblRecoup, this table is linked to a table called tblTRNSPaid

The tblTRNSPaid table has a sum field that provides the total PAID per each Transaction number in the  tluFundsImport table.  

The tblRecoup table is linked to the tblTRNSPaid table by RecFromTRNSNo, which is the TransactionNumber field in the tluFundsImport table, and had a field that calculates the AmtApplied which I have called txtApplied.

I am trying to subtract a field called txtApplied which is on the SubReport from a text field called TotalPaid
on the MainReport.  I need to subtract the total amount for all ProvID that is related to the Reference field.

I have attached a copy of the database.  Thanks again for your help!

Example-EOB.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your form won't open. It's referencing a field named "SSN", which doesn't seem to be available. Before uploading your database, you should make sure that it works correctly, and that you have provided us with explicit instructions as to exactly how to reproduce your issue.

Also, have you tried using the DSUM methods I suggested above? If you get the right fields and tables in your DSUM, you can certainly achieve this. It's best if you work through this yourself, so that you fully understand what's going on.
0
 
Delores_CAuthor Commented:
Sorry about that!  Please choose the frmIndependentRepProcess3 form to open the report, and select all the available dropdown, which is only one (1) of each available, then go to the Design mode.  

I have deleted fields it may ask for which were confidential, please ignore.  Again, I thank you for your patience.

New file attached.
Example-EOB.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay - I can open the form and report, but I don't see the controls where you tried the DSUM and such that I suggested earlier. Can you tell me where you entered those items, and where I can find them?
0
 
Delores_CAuthor Commented:
I am attaching another copy.  I have written some notes on the report to explain the results you will see.  I hope it bring clarification to what I am dealing with.  I belive the many-to-many relationship with the tables is creating a problem.  

The SubReport is related to a many-to-many table, linked by the table tblTRNSPaid, it is linked by the RecFromTRNSNo field (the matching field in the tluFundsImport table, even though the naming is different)

I have created a unbound text box which equals the sum for the field Paid (there are multiple ProvIDs for field Reference) which gives me the total paid for all ProvID related to the Reference field.  I named this calculated field TotalOfAllPaid in the MainReport footer.

I have created a unbound text box which equal the sum for the field AmtApplied in the report footer of the SubReport, which gives me the total of AmtApplied and named that field txtApplied.

I need to subtract the SubReport field txtApplied from the MainReport field TotalOfAllPaid

The MainReport has a link to a table called tblProviderAccount, which has the ProvID number and a Reference field.

The ProvID number is the keyfield, but the Reference links the tluFundsImport table, because the Reference field links all ProvID that has the same Reference.  The information for the MainReport comes from two (2) tables, the tblProviderAccount and the tluFundsImport tables.  

The tluFundsImport table has the TransactionNumber field that is linked to the tblTRNSPaid table, which is a many-to-many relationship. (same as the SubReport)

The SubReport is related to a many-to-many table, linked by the table tblTRNSPaid, it is linked by the RecFromTRNSNo field (the matching field in the tluFundsImport table, even though the naming is different)

Example-EOB.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're going to have to get this information from somewhere else. I would seem that you're looking for the Sum(AmtApplied) for ALL items associated with a specific Plan (Plan 93 in this case).

Can you explain exactly what you need to show in that field? For example, "I need to show the sum of all Amounts for each Plan".
0
 
Delores_CAuthor Commented:
I need to show the sum  of Amounts for each Reference. (The Reference is actually the name of the Provider)

This Provider has many different facilities and each facility has an ProvID number.  The amount sum should equal the total amount for all ProvIDs that is associated to the Reference.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So in your sample data, the table tblProviderAccount has a ProviderName of "Laboratory Corporation", and that Provider is associated with 3 PRoviderIDs - 51519, 51520 and 51521.

Then tblFundsImport is assocated back to tblProviderAccount on ProvID, and your TotalPaid is the Sum(Paid) from tblFundImports.

You need to sum all of the values from tblRecoup.AmtApplied for all Transactions associated with the Provider selected, and then subtract that value from the Sum(tblFundImports.Paid).

Is that correct?




0
 
Delores_CAuthor Commented:
yes, that is exactly what I am trying to do!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay - see if this works.

I built a query that Sums all the AmtPaid for each ProviderID, and then build another query (based on the first query) that Sums the values in THAT query, based on ProviderName (since that seems to be the "unique value" that would define a Provider Account).

I then used a DLookup in the textbox in the footer to locate the value for the ProviderName of that report. So long as you only have a SINGLE ProviderName for a SINGLE report, this should work.


Example-EOB.accdb
0
 
Delores_CAuthor Commented:
Works great, just what I need.  Now I need to implement this into the original to work.  I am guessing I just need to applied the same formula into each report I am using, correct?
0
 
Delores_CAuthor Commented:
I imported the queries and added the txCalmAmt text box, however, what is happening now is that it always gives me the total Amt regardless of the PlanType.  

I guess I should have explained that there are 4 different PlanTypes that are normally in the dropdown option.  Each PlanType has its own report.  An example is if I pick PlanType 93, It will open the report for PlanType 93, and list all the record for that PlanType, under the Provider Name.  I need the formula to only provide the total amounts for each PlanType.  I tried adding the option in your query, but it had no affect on the results.  Can you advise?

0
 
Delores_CAuthor Commented:
I need to add that some of the Provider will not have a subreport attached, because they have not been overpaid.
0
 
Delores_CAuthor Commented:
I really appreciate all your help.  You really deserve more points then I am allow to give.  Thanks Again
0
 
Delores_CAuthor Commented:
I added the PlanType to the script for the text box and it gives me an error.  I have a SQL file that pulls the report by type.  Could this possibly be creating the problem.  Also, I tried to duplicate the query to get the sum of the AmtApplied field, and it will not let create it.  What could be causing this?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I really don't know. YOu shold ask this as a new quetion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.