how to append into multiple tables at same time?

[GeorgeAtkins]

I must have been away too long or my memory is kaput.
I have a flat-file (Excel) I import in Access 2003.
Some of the fields of each record go into a master table, while the other
fields go into a related table.

For example, the imported table contains records consisting of:
Mom FirstName, Mom LastName, Mom Address, Baby DOB, Baby BirthPlace, and
BabyGender.

*The Mom information goes into the master Mom table.
* The Baby information goes into a related Baby table, linked by Mom's
primary key (as defined below):

BabyPK (autonumber)
MomFK (numeric,long)
....other Baby fields

So, hit me upside the head and tell me how to append data from the imported
table into the linked Mom and Baby tables. Can I do this at one time?
If not, how to I append them in and keep the data correctly linked?

Rats. Where did my brains spill?! Thanks for any help.

George

 

[Tom Ellison]

Dear George:

This is an example, in my opinion, of another case where surrogate keys
(autonumbers) confuse an issue, and where the absense of a natural key will
be fatal.

One of the things I presume about your database is that you expect each
mother to be in the Mom table exactly once, with the babies in the Baby
table related to the same mother when it really is the same mother. Now,
how do you expect this to happen? You don't want the same mother added
twice, right? You want the a mother to be found and not added again when
she is already in the table. On what basis will you do this?

These are columns in the Baby table, right?

BabyPK (autonumber)
MomFK (numeric,long)

There is a PK in the Mom table, too, right? That's where all this starts.

Now, if the FirstName and LastName are always typed exactly correctly and
identically, then you can use that as the natural key to make this work. Of
course, it won't be.

Look at you incoming Mom data as having 4 types of rows.

1) rows where the FirstName / LastName match an already recorded Mom and it
is the same person

2) rows where the FirstName / LastName match an already recorded Mom but it
is a different person

3).rows where there is no FirstName / LastName match, but it is a person
already in the Mom table

4) rows where there is no FirstName / LastName match, and it is a new Mom

None of the above can be determined automatically.

Now, after you have somehow, magically or labouriously, added the Moms that
need added and identified the ones that are already added, you can use the
name matches to obtain the autonumber values of the mothers to put into the
baby table.

The computer can show each new mother/baby one at a time and try to find the
proper (possibly) existing mother record, using FirstName, LastName, and
address. More things on which to match would be a good thing. Social
Security Number, Maiden Name, City of Birth, etc. Things that don't change
in a lifetime. First names change, or nick names are used. Marriage
changes last names. People move to a different address. What you have is
very slim to make any identification.

I'm a bit pessimistic that you will be able to make reliable matches at a
high rate. Try it manually for a while first. Is there a single reliable
method?

Tom Ellison

 

[GeorgeAtkins]

Well, I had no intended to overburden my original message with all of these
details, of which you are 100% correct. For my mesage, I was simply assumnig
that the names were new (as they are in most of the cases). It is unfortunate
that there are, in fact, no other verifications available for each mother,
other than name. Even the address could have changed over time, of course.
And it was my idea to try and add births to matched moms.

HOWEVER, let's assume that the moms are always new. Matching of new moms is
not an issue. Now what? Since I do not know what the PK is for each mom, I'm
stuck trying to figure out how to add an import record into both the Mom and
Baby table. Are you saying that I would have to add the moms first, then go
back and match the moms to enter the babies with the correct Mother PK?

 

[Tom Ellison]

Dear George:

If the Baby table is dependent on the Mom table, then the Moms must be added
first. That's a given.

If the Moms' names are unique, you can then retrieve the assigned PK for the
mom to use in the baby row.

If you don't really care, then don't use two tables. Put the mom and baby
in one row in one table. You won't be able to lookup a mom and see 3
babies, as you would if you did care about making the mom an entity.

Just because you tack an autonumber ID on a row doesn't mean it uniquely
identifies the entity with which it is associated. The ID makes for a
theoretically correct database, but doesn't mean it corresponds to the
real-world entities it attempts to represent.

A real-world entity has an identifiable entity uniquely associated with each
row. Your database won't have this to a significant degree without
considerable effort. Your best choice depends on the full set of future
uses against which the database will be tested. If you know all these
future uses, you can work accordingly. If it needs maximum flexibility,
power, and accuracy, then a full-blown effort is essential to make it right
up front.

Good luck, George!

Tom Ellison

 

[GeorgeAtkins]

Thanks again, Tom, for your very useful information. I am famililar with
natural vs artificial keys, so I understand exactly what "unique" means in
this context. That is part of the problem, of course. I'm afraid that the
original data we get is limited to basic information (mom's name and
address). No SSN or other such natural key that can be reliably used.

Here is a further complication: This data is supplied every month. Over
time, a mom COULD have another baby (imagine that!), but she could have
moved. Therefore, only the mom's name would carry over. On the other hand,
there can always be (and are) multiple moms with the same name. Therefore, I
can see no completely reliable way to guarantee uniquess, as you have also
discovered.

But storing the data in a relational system, even manually, should be better
than the flat-file Excel solution. But yeah, it looks like a manual task more
than anything else.

 

[Tom Ellison]

Dear George:

Convincing the uninitiated that the data provided are not sufficient to do
whatever they may later expect it to do can be a serious task. Obviously,
this does not apply to our conversation. Hopefully, you won't be caught
trying to convince others either.

My sympathies in any case. GIGO!!!

Tom Ellison