[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Import Distinct records Access 2003

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.
0
antcal
Asked:
antcal
  • 3
  • 2
1 Solution
 
peter57rCommented:
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.

Pete
0
 
BPebCommented:
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
0
 
antcalAuthor Commented:
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Good luck
0
 
antcalAuthor Commented:
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.
0
 
BPebCommented:
Thanks.

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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now