I need some help... been doing different things and can't seem to get the right result. I don't want to delete duplicate records, but want to sort them together. I have a db with over 40,000 names of customers. Over the years, the customer has gone to our different locations and done business. I want to sort the db by address, which can be about the only unique identifier, and then group the dups together by address.
FName LName Address1 Zip State Amount Date
John Doe 1 Dill Road 33423 KY $15.00 1/1/07
J Doe 1 Dill Road 33423 KY $9.00 7/9/07
Ultimately, I want to have a relationship so when I do the reporting, I can sum up the amounts per person/address.
I guess what I'm looking for is a way to group by the Address1 field. So when you open the table, if a user has more than one entry (according to a dup address), it shows the + symbol next to the record. I've tried doing this by copying the original table, creating a different primary key field for the second table and linking them by the Address 1 field. This didn't work. I feel I'm on the right track, but just can't get what I'm wanting. I want to group these records that have an Address in common so I can producer reports that total the amounts they've paid.
Unfortunately, there isn't a unique ID for each customer since the customer could make purchases at several different locations. The systems that do the transactions are older and not linked. The data I'm working with has come from a spreadsheet. The only common ground I can find is the address. A customer could live on 1 Dill Road, but purchase at many different locations/states/cities.
I know I'm close to getting the records linked, but I'm missing something. Take for example a helpdesk type database that has two tables. One is called incidents and the other could be called comments. From the beginning, linking them would be easy. So for every record, if there was a comment, the table would show a + symbol, showing the relationship. Does that sound right or am I making things more complicated?
I wish there were a unique customer ID, but since there isn't, I want to group by address first (it doesn't matter which address - maybe the first address that was assigned a primary key from autonumber).
Anywhoo.... without a relationship you're not going to get the little plus sign, but you can still group by Address1. Just do it in your report via the sorting and grouping options.
Grouping it in the query is also possible, but you'd only get a total for each group; you wouldn't be able to see each transaction. You can do that by opening it in design and using the Totals button and setting the group row for each field.