Duplicates?

[brownk]

Hello again everyone, I've came across a new duplication problem. I am building a query that pulls loan application information. Ive got everything running properly, but my report is many pages too long due to some "duplicate" records. This is what it looks like

[AppID] [date] [applicant] [collateral] [loan amount]
1108 6/18/12 Joe Dog 1,000,000
1108 6/18/12 Joe Cat 1,000,000
1108 6/18/12 Joe Hamster 1,000,000

I know i am getting duplicates because of several different collateral entries. How would one go about combining the "Collateral" field, or any other field that may have different information for that one particular field?



BTW, the collateral field is not the only field that would have different information for the same loan, i could also have 2 different "Fee" amounts that would be added together.
Thanks in advance

[JoeM]

You can create an Aggregate Query (where you group your records based on your selected fields) and leave the collateral field out of that query.
Take a look at Access's built in help files for more information on Aggregate (Totals) queries.

[brownk]

So this aggregate query will cut down to one entry, with multiple items from the 'collateral' field i listed?

[JoeM]

So this aggregate query will cut down to one entry, with multiple items from the 'collateral' field i listed?

As long as you do not include the "collateral" field in your query, yes.

Another way to do it is to create a simple SELECT query, leaving the "collateral" field off. Then, change to SQL view and add the word "DISTINCT" after SELECT.

Both of these methods combine all duplicate records (duplicates are determined by which fields you are returning in the query, which is why the "collateral" field needs to be left out - if it isn't, they truly are not duplicate records being returned in the query!).

[brownk]

Thanks for your help. You are correct, they are not truly duplicate fields because i have many different collateral entries for that one loan. Lets say loan #111, borrower is Joe, happened on this date for this amount. He offered up his dog, cat, and hamster for collateral. Because of this i have 3 entries for loan #111 even though it is essentially the same exact loan. I'm not so worried about having "duplicate" entries in my query so long as the report has one entry for loan, borrower, ect... and several entries for collateral.

[JoeM]

So are you saying that you want to show collateral? Because if so you don't truly have any duplicate records among the fields you are choosing to display.
Remove it, and then you can collapse them.
Anyway, if you didn't want to remove them and just wanted to show a single record, what would you be showing for collateral? How do you decide which collateral value to show in this single record?

[RayMilhon]

If your concern is the report then leave the query as is. In your report group on the App ID. In the App ID Footer put in all of the fields you want with the exception of the collateral Field. Set the detail Section visible property to false put the collateral field in the detail section. Put an unbound control with all of the fields in your footer section named txtcollateral. Add the following code behind the report.
When you go into the code at the top of the screen you will see "Option Compare Database" Below that add This sets up the variable you will need

Option Compare Database
Option explicit

Private strcollateral as string

Here is the code to build and display the collateral string, Notice that in the detail while building the collateral string you are adding a ", " to the end of the string to get a display of "dog, cat, hamster" however when the group footer prints the field you have to remember to take out the extra ", "

'In the On Format Event of the group header
STRCOLLATERAL = ""

'In the On Format Event of the Detail Section

strcollateral = strcollateral & rs!collateral & ", "



'In the On format Event of the group footer
strcollateral = left(strcollateral,len(strcollateral)-2)
txtcollateral = strcollateral

[brownk]

Yes my only concern is the report. This is what i have so far to show as an example. As you can see, Application 111708 has 3 different entries because it has 3 different collaterals. I would like to see only 1 entry with all the collateral grouped together for that particular application number.

[JoeM]

I would like to see only 1 entry with all the collateral grouped together for that particular application number.

Then go with Ray's suggestion.