1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Duplicate Records in Access Table

Discussion in 'Software Development' started by am1st1, Oct 16, 2008.

Thread Status:
Not open for further replies.
  1. am1st1

    am1st1 Thread Starter

    Joined:
    Sep 3, 2008
    Messages:
    2
    I'm using Microsoft Access 2003.

    I have a table with data containing information related to Customer Number, Customer Name & Product. Some customers have more than one product (so there are multiple records in the table)...example Customer: 1243 has a row for Product: A and another row for Product: C.

    I want to be able to say if Customer 1243 has multiple products than only show me the record for Product C.

    Note: Not all customers have multiple records

    Does anyone have any ideas on how to do this?

    Thanks
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi am1st1,

    Welcome to the forums.

    Firstly, you should Normalise your data. http://en.wikipedia.org/wiki/Database_normalization.

    Your current data, has a relationship of Many-to-Many, a big no no, in properly normalised data - an important paradigm of database design. So you should have a table with your customers, one with your products and then a table linking them together, with the unique ID from your customer table and the unique ID from your products table.

    I'm not sure how to select product C over A without doing it programmatically using VBA, as I do everything that way, however it may be possible, and someone may be able to give you the answer to that one, but you should normalise your data first.

    Failing that, I could write some VBA for you, however, I need to know all the potential options, as well as the data fields, once normalised, the best thing would be to remove any sensitive data, replace it with good dummy data, and then upload the database. If you'd prefer, you can email me the database, just send me a private message, and I'll let you know where to send it.
     
  3. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    MRdNK is right on the money.

    By re-inputting your customer info over and over, you are requiring that each time it is EXACTLY like the last. What would happen if you didn't get it exactly right?

    To keep this simple, let's say you want a simple query to pull all products for customer XYZ Co. You know they have products A,B, and C but your query only returns A. This could be caused if you input the customer name the second time as XYZ Company. The two aren't identical therefore the query return all the actual records it should.

    Having said all that, and assuming you understand the risks, here's one thing to try:

    Create two different queries, the first one pulls just the Customer info. In the query's SQL view, add the word UNIQUE right after SELECT. This should return only one record for each customer.

    Create the second query and include only the Customer Number for the customer info, then add the product info. Then use the Grouping button and in the grid section, select MAX for the grouping option on the product.

    Now create a third query but instead of tables, just bring in those two queries. Create a relationship, one to many, between the Customer numbers of both queries.

    I've just worked this out in my head, so it's theoretical, but it should work, depending on how you input your products.

    Good luck,

    MBN
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759799

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice