Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Location
    OHIO
    Posts
    3

    Unanswered: Duplicate Records and grouping them

    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.
    For example:
    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.


    Any ideas?
    Thanks,
    ER

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT FName 
         , LName 
         , Address1 
         , Zip 
         , State 
         , Amount 
         , Date
      FROM daTable
    ORDER
        BY Address1
         , LName 
         , FName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Location
    OHIO
    Posts
    3
    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.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could group by Address1, but it would be pointless imo... since the address could be 1 Dill Rd or the customer could move.

    The only accurate way to find out a summary of what activity a given customer has is to have a Customer ID for each customer.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2008
    Location
    OHIO
    Posts
    3
    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).

    Thanks in advance!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yuk. Horrible situation really.

    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •