Import Distinct records Access 2003

Posted on 2006-05-24
Last Modified: 2012-08-14
I am running short on time for this project, and most of the the similar issues don't quite match my issue.

I have a montly inventory file with thousands of records.  Due to some typo's in diffrent fields, I am not getting the proper distinct listing.

What I want is a distinct NDC number (it_ndc) from the raw data, witht the first "distinct" listing I also want to import the "itemdescr", "it_strn", "it_size", "it_pack".  The NDC numbers are unique to a drug, and I can fix the minor typo's once I import the data and create a reference table.

To get a unique NDC record I tried:

SELECT DISTINCT RawMonthly.it_ndc, RawMonthly.itemdescr, RawMonthly.it_strn, RawMonthly.it_size, RawMonthly.it_pack
FROM RawMonthly;

Due to the typo's in other fields I may get an NDC number more then once (because the whole line is distinct).
I tried

SELECT DISTINCT RawMonthly.it_ndc
FROM RawMonthly;

This gave me the unique NDC numbers, but I had problmes trying to join tables to import the rest of the columns.
I have tried a lot of variations, but I can't get what I need and time is running short.
Basically I need one import to create a reference table, I can set the data types once, and I should be able to move forward after I have the reference table.

Thank you in advance.
Question by:antcal
    LVL 77

    Expert Comment

    Hi antcal,
    It depends on whether it matters which values you pick up for the other fields, but if not then you can do:

     SELECT  RawMonthly.it_ndc, max(RawMonthly.itemdescr), max(RawMonthly.it_strn),
     max(RawMonthly.it_size), max(RawMonthly.it_pack)
     FROM RawMonthly
    Group  By RawMonthly.it_ndc

    Of  course this will not pick up all the values from the same record, as different records could hold max values for different fields.

    LVL 5

    Accepted Solution

    Load your reference table (NDC_Unique) with:

    SELECT  it_ndc
      FROM RawMonthly
       Group By it_ndc

    That will give you the unique values you want.  Make sure your new table has the it_ndc defined the same as it is in RawMonthly.  It's been awhile since I've played with NDC codes but aren't they like 11 digits long, but can also include a dashes and suffix values?  If so then make sure you use text fields for both and trim the values so they format the same.  That may the problem with your attempted join and may require you to sweep the data to remove spaces at the end of the NDC codes in both tables.

    Good luck

    Author Comment

    Peter57r, thank you for responding, but I need the information directly assoicated with NDC, so I need info from the same line.

    BPeb, thank you for the repsonse.

    This is the code Access spit out:

    SELECT RawMonthly.it_ndc, RawMonthly.itemdescr, RawMonthly.it_strn, RawMonthly.it_size, RawMonthly.it_pack
    FROM RawMonthly
    GROUP BY RawMonthly.it_ndc, RawMonthly.itemdescr, RawMonthly.it_strn, RawMonthly.it_size, RawMonthly.it_pack;

    It appears to do what I want, but it is the end of the day, and I will pour over the data tonight or tomorrow. My only concern was that the numerous listings on the "Group By" line may skew things.  I will update you soon, but this looks like a winner.
    LVL 5

    Expert Comment

    Yeah you'll have to leave those other things out to get the uniqueness you need.  Never fun cleaning up data.

    Good luck

    Author Comment

    I used the Group by, but I still ended up using excel's subtotals to find the dupes, then I re-imported the data.
    I hope I find a better option in the near future as I'll have to compair NDC's in my Key Table with future data imports to find new (un-keyed) NDC numbers.

    Thanks for your help on this issue.
    LVL 5

    Expert Comment


    For the future you might consider putting some of the steps in code to simplify your work.  If you've set up your new table to use the NDC as a primary key then when you get new files you should be able to run the Group By SQL to append to it.  That will get you any new NDC's you have to deal with.  Also try creating a dupe check query (maybe even more than one depending on what fields trapping on) in Access that you can point to the imported data.  That way you can flag the ones that need work using code instead of all those manual steps.

    Just a thought.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now