Import to multiple linked tables

[Guest]

It sounds like the preferred way to use access is to use mutlple linked
tables rather than repeating data - in simplified form:
table Main
key: autonumber
userID: long integer (used in a relation with users key; this is the
"many" side)
asset: text (actually, there's a whole bunch of data for each asset)

table Users
key: autonumber
user: text (actually, a user has a phone and much more)

Where I may have multiple assets per user. My problem with this approach is
importing data. The original data source is typically an Excel spreadsheet
that includes the user and the asset data on each line.
How do a build a query or a form for inputing (or VBA or whatever) that will
pull the data into both tables at once? The standard approach seems to be one
query to add assets, and a separate query to add users, and more queries to
handle the cases where users leave (their assets have to assigned to someone
else) and data is imported where the spreadsheet has both old users and new
users that don't appear in the Users table. That seems like a lot of work
just to avoid a bit of redundant data.

The alternate approach (although it requires that redundant data be stored)
would be to just store all the user's data with each asset, and use the Users
table to prepopulate the data for the entry of an Asset with a command
button.

 

[Nikos Yannacopoulos]

There are ways (macros or VBA code) to automate several action queries
to run at a click of a single button, or even upon database open
without any user action required, and this is indeed the way to go.

If you ever find someone to advise in favor of single flat table / data
redundancy, just don't take any advice on Access from them (or any
database for that matter).
It would take too long to argue on the advantages of data normalization,
but a web search on the term will return plenty of articles on the
subject; read a few and I'm sure you will be convinced.

HTH,
Nikos

 

[twas]

I have found lots of reasons that I should use multiple tables rather than a
flat database.
However, I have not figured out how to import from a flat file into a
relational database.

If the file has
User Asset
George Dell
Karen GW

how do I import into the linked database so that I end up with George and
Karen as users,
(with some ID, say 4 for George and 5 for Karen), and then
so
UserID User
4 George
5 Karen
as the result in the Users table
and
ID UserID Asset
1 4 Dell
2 5 GW
in the Asset table??

thanks
Twas

 

[Nikos Yannacopoulos]

Make a query on the linked spreadsheet, returning just the username (or
any other user-related fields, additionally); make it a Totals query
(Query > Totals), use Group By totals function, so you get each user
once; now make that an append query on the users table. In the users
table, set the username field to indexed - no duplicates. This way the
query will only add new users. This is the first one to run.

Make a second query combining the linked spreadsheet and the users
table, joined on username. Make it return the UserID field only from the
users table, and the asset-related fields only from the linked
spreadsheet; that should only give you one row per asset anyway, without
the need for totals. Make it append to the assets table. You will need
to drop your existing PK field in the assets table and make a composite
PK on the UserID and Asset fields, so you automatically drop duplicates.
Run this query after the previous one.

Be warned that the whole process is very vulnerable to (a) spelling
mistakes in both user and asset names, and (b) duplicate user names (how
many John Smiths could you have?). Unless you can make it more robust
(e.g. by using SSN's for people and asset numbers for assets, assuming
you have those, in both the spreadsheet and Access), it will take a lot
of manual inspection and corrections.

HTH,
Nikos