Importing from Excel

[m]

....a popular topic, I know. I failed to find past postings that deal
with what I would like to do.

First, a question: Should I use Excel to clean-up data (eliminate
duplicates, etc.) or import into temp tables in Access and do it
there? My gut feeling is that it is far simpler in Excel. Then
again, I am not as proficient in Access as I am in Excel.

Here's the job:
We've assembled a list of businesses as a flat table. The usual stuff
is there: name, address, contact, etc. That's the easy part.

However, each and every one of these "records" has a one-to-many
relationships to other fields. As a hypothetical example, ABC, Inc.
might sell nuts, bolts and brooms. The way this is documented in the
Excel sheet is that there are three separate entries for ABC, Inc. and
each of these is exactly equal except for the "items sold" column. We
have a few examples of this.

Also, If I wanted to normalize such things as city, state, country, in
theory this would be a relationship to another table in Access
containing lists of cities, states and contries.

So...what is the right approach to importing something like this as a
matter of procedure? Do we input the flat file and start hacking away
in Access or try to create all the tables in Excel? What is the
easiest? Writing VBA code isn't a problem I've done tons of that
under Excel.

Thanks,

-Martin

 

[Jeanette Cunningham]

martin,
the second question about normalising -
There are several examples of how to use a union query to create a
normalised table from parent and child data in the same column on this
discussion group, that I have noticed recently.
Do a search on 'normalising union query'.

I recommend that you clean out any duplicates in whichever program is the
easiest for you.
There are probably others who would say to use access to find duplicates
<g>.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Peter Hibbs]

Martin.

Have a look at the Excel to Access Converter program at :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

which may do what you want.

Peter Hibbs.

 

[m]

Have a look at the Excel to Access Converter program at :-

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Exce...

It sure looks like it does. Thanks. I'll create a test table and
give it a shot.

-Martin