Re: how to append into multiple tables at same time?

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" wrote:

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" <GeorgeAtkins@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BC4B9DB1-3A89-4CEA-AB23-37CF39E36CBF@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • I survived Soviet health care--barely.
    ... many Americans and Brits think nothing is scarier than war. ... take Mom to the hospital when her contractions started on Feb. 25, ... People stopped yelling at my mother then, ... "I don't hear the baby," said the old doctor who was on duty. ...
    (soc.culture.argentina)
  • Re: 9 day old infant sexually assaulted, dies
    ... lunatic, raping a newborn, then beating the mother on the way to the ... .....Lincoln Park Police Chief Thomas Karnes said he believes the baby was ... At least this mom didn't let the kid lay there, ...
    (alt.true-crime)
  • Re: how to append into multiple tables at same time?
    ... No SSN or other such natural key that can be reliably used. ... If the Baby table is dependent on the Mom table, ... back and match the moms to enter the babies with the correct Mother PK? ...
    (microsoft.public.access.queries)
  • Re: how to append into multiple tables at same time?
    ... mother to be in the Mom table exactly once, with the babies in the Baby ... These are columns in the Baby table, ... There is a PK in the Mom table, too, right? ... then you can use that as the natural key to make this work. ...
    (microsoft.public.access.queries)
  • Re: UPDATE On Boys Found in Car Trunk
    ... No, it's a mom thing, calling your dead child "my baby". ... My guess is the neighbor is also a mother, ...
    (alt.true-crime)