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.
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.
ASKER
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.
= 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.
"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)
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)
ASKER
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?
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?
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.
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.
ASKER
It is only giving the last amounts in the results. I need the sum of all amounts for this group.
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.
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.
ASKER
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.
ASKER
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
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
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.
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.
ASKER
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
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
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?
ASKER
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
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
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".
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".
ASKER
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.
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.
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?
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?
ASKER
yes, that is exactly what I am trying to do!
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
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
ASKER
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?
ASKER
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?
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?
ASKER
I need to add that some of the Provider will not have a subreport attached, because they have not been overpaid.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I really appreciate all your help. You really deserve more points then I am allow to give. Thanks Again
ASKER
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?
I really don't know. YOu shold ask this as a new quetion.
= 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.