I have a report which totals dollar amounts from claim numbers. I don't want dollar values which come from the same claim number included in the total. I know how to set the hide duplicates property but the number is still included in the total. How do I omit these values?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Omit duplicate values in calculation 1
- Thread starter Chew407
- Start date
-
1
- #2
Chew407
Is this the scenario?
Claim #1, Amount $1000
Claim #1, Amount $1000
Claim #2, Amount $5000
And you want the report to show the total value for each claim...so that Claim #1 would have a total of $2000, and Claim #2 would have a total of $5000?
If that's the case use a Totals query to populate your report.
Otherwise, I'm not sure I understand what you are referring to as duplicate values. Unless Claim #1 actually had two separate amounts of $1000, why would there be two separate entries? Is there a problem with the manner in which the data is being entered? Or a normalization problem?
Tom
Is this the scenario?
Claim #1, Amount $1000
Claim #1, Amount $1000
Claim #2, Amount $5000
And you want the report to show the total value for each claim...so that Claim #1 would have a total of $2000, and Claim #2 would have a total of $5000?
If that's the case use a Totals query to populate your report.
Otherwise, I'm not sure I understand what you are referring to as duplicate values. Unless Claim #1 actually had two separate amounts of $1000, why would there be two separate entries? Is there a problem with the manner in which the data is being entered? Or a normalization problem?
Tom
- Thread starter
- #3
You pretty much have the idea. Here is what I'm looking at more specifically:
Invoice
Claim # Amount Recovered Date
100789 $3000 $1000 9/9/05
100789 $3000 $1000 9/2/05
The claim number 100789 can appear twice in the report but on different days. These are two seperate records, not duplicates. In the report the invoice amount would total $6000 but it is actually only $3000. The total Recovered for that claim should be $2000 however. Have any ideas how to do this?
Invoice
Claim # Amount Recovered Date
100789 $3000 $1000 9/9/05
100789 $3000 $1000 9/2/05
The claim number 100789 can appear twice in the report but on different days. These are two seperate records, not duplicates. In the report the invoice amount would total $6000 but it is actually only $3000. The total Recovered for that claim should be $2000 however. Have any ideas how to do this?
Chew407
It seems to me you need to make a bit of a change in your table design.
I'm not sure of your current table names, but let's assume that you have one table called tblClaims. The same Claim # should never appear more than once in that table.
For a simple example, here's the way it should be...
tblClaims
ClaimID (an AutoNumber field)
ClaimNbr (either a Number of a Text field)
ClaimDate (a Date/Time field)
Amount (a currency field)
Then you would have a second table to enter recovered amounts...
tblPayments
PaymentID (an AutoNumber field)
ClaimNbr (a foreign key)
Amount (a currency field)
PaymentDate (a Date/Time field)
Then in the Relationship window, you would join the two tables together in a one-to-many relationship on the ClaimNbr field.
If you do it that way, then the initial amount of the Claim 100789 will always be $3000, and the balance will reduce each time a recovery payment is made. But you don't need to store the balance because you can always calculate it in a query.
The next step would be to set up a form, based on tblPayments, in which you enter the recovery payments.
Your current problem is that you have two, or maybe more, rows in the Claims table with the same claim #. To Access, these appear as separate entries, because even though they have the same Claim # there is a different Date etc.
Hope that helps.
Tom
It seems to me you need to make a bit of a change in your table design.
I'm not sure of your current table names, but let's assume that you have one table called tblClaims. The same Claim # should never appear more than once in that table.
For a simple example, here's the way it should be...
tblClaims
ClaimID (an AutoNumber field)
ClaimNbr (either a Number of a Text field)
ClaimDate (a Date/Time field)
Amount (a currency field)
Then you would have a second table to enter recovered amounts...
tblPayments
PaymentID (an AutoNumber field)
ClaimNbr (a foreign key)
Amount (a currency field)
PaymentDate (a Date/Time field)
Then in the Relationship window, you would join the two tables together in a one-to-many relationship on the ClaimNbr field.
If you do it that way, then the initial amount of the Claim 100789 will always be $3000, and the balance will reduce each time a recovery payment is made. But you don't need to store the balance because you can always calculate it in a query.
The next step would be to set up a form, based on tblPayments, in which you enter the recovery payments.
Your current problem is that you have two, or maybe more, rows in the Claims table with the same claim #. To Access, these appear as separate entries, because even though they have the same Claim # there is a different Date etc.
Hope that helps.
Tom
- Thread starter
- #5
Wow! That's pretty much exactly the way I have it set up. I have two different tables, one where the claim number appears only once and another table called tblRecovered where the values under the Recovered column in my report come from. My dilema is that my query allows me to retrieve data based on the same claim number in the two tables yet there can be more than one recovery for the same claim number. Say a claim of a total of $3000 was collected in two parts, on two different days, from different insurance companies - $1500 on one day, $1500 on the other. How do I show these two separate values in the table, total them and avoid adding the invoice amount for the same claim?
Chew407
I'm not sure what you call your table that stores the claim numbers, so I will call it tblClaims. In that table you will have the claim #, claim amount, date, etc.
In tblRecovered, you will have the claim #, amount recovered and date.
In your query, show the two tables, joining them on the Claim # field. Double click the arrow that joins those two fields, and make sure that the query is selecting all the records from tblRecovered, and only those from tblClaims where the Claim #s are equal (one to many)
Base your report on that query. You don't have to store the totals. You can do the totals in the report.
Another way to achieve this would be to design your report a bit differently. Base the main report on tblClaims and then bring in a SubReport based on tblRecovered. The main report and the SubReport would be linked on the Claim #, with the Master being in the main report and the Child being in the subreport.
Tom
I'm not sure what you call your table that stores the claim numbers, so I will call it tblClaims. In that table you will have the claim #, claim amount, date, etc.
In tblRecovered, you will have the claim #, amount recovered and date.
In your query, show the two tables, joining them on the Claim # field. Double click the arrow that joins those two fields, and make sure that the query is selecting all the records from tblRecovered, and only those from tblClaims where the Claim #s are equal (one to many)
Base your report on that query. You don't have to store the totals. You can do the totals in the report.
Another way to achieve this would be to design your report a bit differently. Base the main report on tblClaims and then bring in a SubReport based on tblRecovered. The main report and the SubReport would be linked on the Claim #, with the Master being in the main report and the Child being in the subreport.
Tom
- Thread starter
- #7
Tom,
I believe my query is one-to-many. It's stored in a variable and used as my RecordSource for the report - under the On Open event property:
The table you have called tblClaim is my tblIncident. Don't I have the join right?
I believe my query is one-to-many. It's stored in a variable and used as my RecordSource for the report - under the On Open event property:
Code:
Private Sub Report_Open(Cancel As Integer)
strRptAmt = "SELECT tblRecovered.CL_CLAIMNO, tblIncident.INV_AMOUNT, tblIncident.IDENT_STAMP, " _
& "tblRecovered.AMOUNT, " _
& "tblRecovered.DATE, tblRecovered.CHEQUE_NO, tblRecovered.RECEIVED_SOURCE, " _
& "tblRecovered.JOURNAL_NO, tblRecovered.JOURNAL_DATE, tblRecovered.COMMENTS " _
& "FROM tblRecovered LEFT JOIN tblIncident ON " _
& "tblRecovered.CL_CLAIMNO = tblIncident.INCIDENT_ID WHERE (((tblRecovered.DATE) " _
& "Between #" & dtFROM & "# And #" & dtTO & "#)) ORDER BY tblRecovered.CL_CLAIMNO;"
Me.RecordSource = strRptAmt
End SubThe table you have called tblClaim is my tblIncident. Don't I have the join right?
Chew407
I will make up a little test table and see if I can figure out where the problem lies. Your SQL doesn't look right to me.
There is another potential problem. I notice that one of your fields in tblRecovered is DATE. You should not use that as a field name because DATE is a reserved word in Access. That can mess things up.
I'm assuming that the #" & dtFROM & "# and #" & dtTO & "# are parameters that pop up...or are these being drawn from somewhere else? Setting up a small form in which the FROM and TO dates are entered is a better approach.
Anyway, I will get back to you. In the meantime, try changing DATE to MyDate or something else that makes sense to you.
By the way, where are you located? Since you spell CHEQUE, rather than CHECK, you must be from either Canada or England, not US. I am from Ontario.
Tom
I will make up a little test table and see if I can figure out where the problem lies. Your SQL doesn't look right to me.
There is another potential problem. I notice that one of your fields in tblRecovered is DATE. You should not use that as a field name because DATE is a reserved word in Access. That can mess things up.
I'm assuming that the #" & dtFROM & "# and #" & dtTO & "# are parameters that pop up...or are these being drawn from somewhere else? Setting up a small form in which the FROM and TO dates are entered is a better approach.
Anyway, I will get back to you. In the meantime, try changing DATE to MyDate or something else that makes sense to you.
By the way, where are you located? Since you spell CHEQUE, rather than CHECK, you must be from either Canada or England, not US. I am from Ontario.
Tom
- Thread starter
- #9
Chew407
Okay, here's what I did. I made up two small tables. I didn't use all the fields you show because some of them (IDENT_STAMP etc.) aren't clear to me.
tblIncident
INCIDENT_ID (AutoNumber field), Indexed, No duplicates
CL_CLAIMNO (Number field), Indexed, No duplicates
CLAIM_DATE (Date/Time)
AMOUNT (Currency)
tblRecovered
CL_CLAIMNO (Number field), same # as in tblIncident
CL_DATE (Date/Time), this is the date of recovery payment
AMOUNT (Currency), this is the amount of recovery payment
RELATIONSHIPS: In the Relationships window, I joined the two tables on CL_CLAIMNO, checked Referential Integrity, and the join is one-to-many. Note that the CL_CLAIMNO in tblIncident has to be indexed with no duplicates in order for this to work.
In tblIncident, I entered 4 claims. With claim numbers 12345, 23456, 34567 and 45678. I gave them dates and Amounts.
Then I built a form, frmIncidentRecovered, based on tblIncident. Into that form I pulled a SubForm based on tblRecovered. This gives you a handy way to keep individual claims and their recovery payments together.
Then I built a second form, an unbound form called frmDateSelect. This has on it 2 text boxes and a command button. The text boxes are called txtStartDate and txtEndDate. The command button is used to run the report I built...see three paragraphs below.
Then I built a query, called qryIncidentRecovered. This has in it the two tables, tblIncident and tblRecovered. On the relationship line, you have two choices...(a) if you want all claims between selected dates to show in a report, make sure that the query is called to show all records from tblIncident and only those from tblRecovered where the joined fields are equal, or (b) if you only want to show claims where payments have been made between the selected dates, then make sure that the query is called to show only those where the join fields are equal.
The SQL for the query is
Then I built a report, rptIncidentRecovered based on the query, pulling in the fields I wanted to show. Then I grouped on the CL-CLAIMNO field.
The way it works is this. You drive everything from frmDateSelect. You enter the StartDate and the EndDate, and then press the Command button which runs the report based on the query.
If you want, you can put an unbound text box in the Report Header, with its control source something like ="This report covers claims and recovered payments between " & Forms!frmDateSelect!txtStartDate & " and " & Forms!frmDateSelect!txtEndDate & "."
I didn't bother, in my test case scenario, to add labels etc. to make the report prettier, but this can easily be done. You can also add totals in the report footer, etc., as you choose.
Hope this model helps. You can, if you wish, create the report based on an SQL that runs OnOpen, but then you have to deal with the parameters. The model and method I have described works a whole lot better and smoother in the long run and you don't have to deal with the pop-up parameters.
Post back if you need something clarified further.
I sort of figured from the 407 in your handle that you might be from somewhere close to Toronto. I live in Guelph.
Tom
Okay, here's what I did. I made up two small tables. I didn't use all the fields you show because some of them (IDENT_STAMP etc.) aren't clear to me.
tblIncident
INCIDENT_ID (AutoNumber field), Indexed, No duplicates
CL_CLAIMNO (Number field), Indexed, No duplicates
CLAIM_DATE (Date/Time)
AMOUNT (Currency)
tblRecovered
CL_CLAIMNO (Number field), same # as in tblIncident
CL_DATE (Date/Time), this is the date of recovery payment
AMOUNT (Currency), this is the amount of recovery payment
RELATIONSHIPS: In the Relationships window, I joined the two tables on CL_CLAIMNO, checked Referential Integrity, and the join is one-to-many. Note that the CL_CLAIMNO in tblIncident has to be indexed with no duplicates in order for this to work.
In tblIncident, I entered 4 claims. With claim numbers 12345, 23456, 34567 and 45678. I gave them dates and Amounts.
Then I built a form, frmIncidentRecovered, based on tblIncident. Into that form I pulled a SubForm based on tblRecovered. This gives you a handy way to keep individual claims and their recovery payments together.
Then I built a second form, an unbound form called frmDateSelect. This has on it 2 text boxes and a command button. The text boxes are called txtStartDate and txtEndDate. The command button is used to run the report I built...see three paragraphs below.
Then I built a query, called qryIncidentRecovered. This has in it the two tables, tblIncident and tblRecovered. On the relationship line, you have two choices...(a) if you want all claims between selected dates to show in a report, make sure that the query is called to show all records from tblIncident and only those from tblRecovered where the joined fields are equal, or (b) if you only want to show claims where payments have been made between the selected dates, then make sure that the query is called to show only those where the join fields are equal.
The SQL for the query is
Code:
SELECT tblIncident.INCIDENT_ID, tblIncident.CL_CLAIMNO, tblIncident.CLAIM_DATE, tblIncident.AMOUNT, tblRecovered.CL_DATE, tblRecovered.AMOUNT
FROM tblIncident LEFT JOIN tblRecovered ON tblIncident.CL_CLAIMNO = tblRecovered.CL_CLAIMNO
WHERE (((tblRecovered.CL_DATE) Between [Forms]![frmDateSelect]![txtStartDate] And [Forms]![frmDateSelect]![txtEndDate]));Then I built a report, rptIncidentRecovered based on the query, pulling in the fields I wanted to show. Then I grouped on the CL-CLAIMNO field.
The way it works is this. You drive everything from frmDateSelect. You enter the StartDate and the EndDate, and then press the Command button which runs the report based on the query.
If you want, you can put an unbound text box in the Report Header, with its control source something like ="This report covers claims and recovered payments between " & Forms!frmDateSelect!txtStartDate & " and " & Forms!frmDateSelect!txtEndDate & "."
I didn't bother, in my test case scenario, to add labels etc. to make the report prettier, but this can easily be done. You can also add totals in the report footer, etc., as you choose.
Hope this model helps. You can, if you wish, create the report based on an SQL that runs OnOpen, but then you have to deal with the parameters. The model and method I have described works a whole lot better and smoother in the long run and you don't have to deal with the pop-up parameters.
Post back if you need something clarified further.
I sort of figured from the 407 in your handle that you might be from somewhere close to Toronto. I live in Guelph.
Tom
- Thread starter
- #11
Chew407
I had some time to look at this again this evening, so I need to make a change to my suggestions from this afternoon. It has to do with the report.
Rather than having one report based on qryIncidentRecovered, you need to make a main report and a subreport. Here's what to do...
1. Create a main report based on tblIncident.
2. Using the wizard, drag in a subreport based on either (a)tblRecovered, or (b)qryIncidentRecovered.
3. In the main report, do a Group on CL_CLAIMNO. Add Group Headers and/or Group Footers as you would prefer.
If you do it this way, then you will always have the data from the tblIncident showing only once (even if no recovery payments have been made), but the subreport will show as many recovery payments from tblRecovered as are needed.
If you don't want your report to show those claims where no recovery payments have been made, we have a little more work to do yet, and it will involve creating a Union query and then basing the report on that.
Let me know how you make out.
Tom
I had some time to look at this again this evening, so I need to make a change to my suggestions from this afternoon. It has to do with the report.
Rather than having one report based on qryIncidentRecovered, you need to make a main report and a subreport. Here's what to do...
1. Create a main report based on tblIncident.
2. Using the wizard, drag in a subreport based on either (a)tblRecovered, or (b)qryIncidentRecovered.
3. In the main report, do a Group on CL_CLAIMNO. Add Group Headers and/or Group Footers as you would prefer.
If you do it this way, then you will always have the data from the tblIncident showing only once (even if no recovery payments have been made), but the subreport will show as many recovery payments from tblRecovered as are needed.
If you don't want your report to show those claims where no recovery payments have been made, we have a little more work to do yet, and it will involve creating a Union query and then basing the report on that.
Let me know how you make out.
Tom
- Thread starter
- #14
Tom,
Thanks a lot for the suggestion. It also gave me some ideas for other reports I have in the works. I tried using a subreport based on tblRecoverd as you suggested. For the main report I used the same SQL I used before, without the fields from tblRecovered. This worked fine. The only problem is how do I do the calculations using the invoiced amounts from the main report and the recoverd amounts from the subreport? I need to subtract these numbers to get the amount outstanding for each individual claim. I'm having trouble wrapping my head around this one!
Thanks a lot for the suggestion. It also gave me some ideas for other reports I have in the works. I tried using a subreport based on tblRecoverd as you suggested. For the main report I used the same SQL I used before, without the fields from tblRecovered. This worked fine. The only problem is how do I do the calculations using the invoiced amounts from the main report and the recoverd amounts from the subreport? I need to subtract these numbers to get the amount outstanding for each individual claim. I'm having trouble wrapping my head around this one!
Chew407
Here's what to do to get the balances.
In your subreport based on tblRecovered
1. Group By your Claim #
2. In that Group Footer, put an unbound text box called txtTotal, with the control source as the following expression...
=Sum([tblRecovered].[AMOUNT])
That will provide the total of recovered amounts.
You may wish to put a label that shows "Total Recovered" or whatever you choose.
In your main report
In either the Detail section or the Group Footer section, put an unbound text box called txtBalance, with the control source as the following expression...
=[tblIncident.AMOUNT]-IIf([qryIncidentRecovered subreport1].Report.HasData=-1,[qryIncidentRecovered subreport1]!txtTotal,0)
Again, you may wish to put a label that shows "Balance" or something like that.
Here's the thing about this expression. You need to test for HasData, or in those cases where nothing has been recovered you will get an error. The -1 in the expression is Access' version of True.
I am, of course, using the table, report and subreport names that I made up. You will have to change those to the appropriate names of your tables, report and subreport.
The key is referencing the subreport properly.
We have been on vacation near Parry Sound. I am returning home to Guelph in an hour or so. Therefore I won't be able to check back on this until later this afternoon, but will do so then.
Keep me posted as to how you are doing.
Tom
Here's what to do to get the balances.
In your subreport based on tblRecovered
1. Group By your Claim #
2. In that Group Footer, put an unbound text box called txtTotal, with the control source as the following expression...
=Sum([tblRecovered].[AMOUNT])
That will provide the total of recovered amounts.
You may wish to put a label that shows "Total Recovered" or whatever you choose.
In your main report
In either the Detail section or the Group Footer section, put an unbound text box called txtBalance, with the control source as the following expression...
=[tblIncident.AMOUNT]-IIf([qryIncidentRecovered subreport1].Report.HasData=-1,[qryIncidentRecovered subreport1]!txtTotal,0)
Again, you may wish to put a label that shows "Balance" or something like that.
Here's the thing about this expression. You need to test for HasData, or in those cases where nothing has been recovered you will get an error. The -1 in the expression is Access' version of True.
I am, of course, using the table, report and subreport names that I made up. You will have to change those to the appropriate names of your tables, report and subreport.
The key is referencing the subreport properly.
We have been on vacation near Parry Sound. I am returning home to Guelph in an hour or so. Therefore I won't be able to check back on this until later this afternoon, but will do so then.
Keep me posted as to how you are doing.
Tom
- Thread starter
- #16
Tom,
I've tried this method and it's not working the way I want it to. I've pretty much given up this approach. This db is giving me a headache. So, I'm going to try and do the calculations within the code before the report is opened. Now the challenge is to open the necessary recordsets to call on the appropriate incident data when the recovery data is being entered. This way I can subtract what was recovered from the total amount invoiced and keep that number up to date.
I've tried this method and it's not working the way I want it to. I've pretty much given up this approach. This db is giving me a headache. So, I'm going to try and do the calculations within the code before the report is opened. Now the challenge is to open the necessary recordsets to call on the appropriate incident data when the recovery data is being entered. This way I can subtract what was recovered from the total amount invoiced and keep that number up to date.
Chew
Is there any chance you can send the database to me...removing all sensitive data before doing so, but leaving a few records (or plugging in dummy records) so that I have something to work with?
The approach I suggested will work well. I suspect it's a matter of referencing the report and subreport in the correct manner. I'd be glad to help.
Also, are you using Access 2000, or Access XP, or 2003?
If you are interested in sending the database, let me assure you that I am not the least bit interested in the data or any personal information.
I can't post my e-mail address on this site, for fear of gathering up spammers. But you can interpret the following...
twatson"symbol for at"sentexdotnet"
Tom
Is there any chance you can send the database to me...removing all sensitive data before doing so, but leaving a few records (or plugging in dummy records) so that I have something to work with?
The approach I suggested will work well. I suspect it's a matter of referencing the report and subreport in the correct manner. I'd be glad to help.
Also, are you using Access 2000, or Access XP, or 2003?
If you are interested in sending the database, let me assure you that I am not the least bit interested in the data or any personal information.
I can't post my e-mail address on this site, for fear of gathering up spammers. But you can interpret the following...
twatson"symbol for at"sentexdotnet"
Tom
- Status
Similar threads
- Locked
- Question
- Locked
- Question