Split a TABLE into a linked 1 to many

[Jen0dorf]

Hi
Sorry to be long winded but I’m trying to supply all relevant information

Can I just run this past you clever people and test my logic

As it stands I have been given a database with a table with the following fields
Mlink
Plot


GraveNo
Links
Surname
Forenames
DayofDeath
MonthofDeath
YearofDeath
DayofBurial
Mon thofBurial
YearofBurial
Age
Inscription
PP
Notes

For various reasons discussed in other posts this is not condusive to the searches required

So I need to split the table into two tables
One will have the fields

graveID
Plot
GraveNo
Mlink

The other:
DesceasedId
Surname
Forenames
Dayofdeath
Monthofdeath
Yearofdeath
Dayofburial
Monthofburial
Yearofburial
Age
Ppnote
fkGraveID

TheGraveId from table one is linked to fkGraveId in table 2 as a one to many, the grave ID and the desceased ID are the indexes.

I have 32000 records so an import is vital.
What I thought I’d do is

1. Take the original table and add the additional field deceasedID and fkGraveID
2. Then make the DeseasedID my key field.
3. Duplicate that table
4. 4 delete all the fields that are not listed in the first table
5. Make GraveId my key in that table.

I think that this way I would create my linked tables
Is my logic correct?

Thanks

Ian

[June7]

Not quite.

Do you want to replace GraveNo with autonumber PK/FK?

You will need the GraveNo field as a temporary link between the tables while you make this change.

First build tblGraves with the necessary fields and then populate them so there is a unique record for each grave.

INSERT INTO tblGraves(Plot, GraveNo, Mlink) SELECT DISTINCT Plot, GraveNo, Mlink FROM oldtable;

(or run MAKE TABLE query that will create and populate tblGraves in one action)

Add the new fkGraveID field then run an UPDATE query to populate the new GravesID values into the oldtable.

UPDATE oldtable SET fkGravesID=tblGraves.GraveID WHERE oldtable.GraveNo=tblGraves.GraveNo;

Once all is good, can delete GraveNo from oldtable and rename as tblDeceased.

Do this on a copy of db.

[Jen0dorf]

Hi

thanks for the info

Sounds complicated to me but I'm sure to you it's boiling an egg.

My project for tomorrow

cheers

Ian

[Jen0dorf]

Hi

think I may have confused myself and in turn everyone else

In order to perhaps make it clearer I have posted screen shots of my new table structure ( created with help from the forum which will allow better searches) and the table design of the single table I am concerned with at the moment. ( I do have another table with memorials but one problem at a time)

I do not want to make the GraveNo into a FK/PK as I think this would not work.

I have created a field called GraveID in the graves table and linked that to FK_GraveID in the deceased table.

What I need to do is import the 35000 records and split the fields between the two tables as per the relationship chart.


I spent the day following your directions and either run out of resources or frozon access or created a non searchable database.

( Thanks for the reminder to backup)

Am I making this too complicated?

thanks

Ian

[June7]

Why could GraveNo not be a PK/FK?

Cannot link GraveID as PK/FK until the field in tblDeceased is populated.

GraveNo must be temporary link until this accomplished, in fact, don't even establish relationship links until data is in order.

[Jen0dorf]

Hi

the reason is that the GraveNo is not unique.

In each of the 24 plots there is a grave1, grave 2 etc etc

I assumed that as they are not unique they could not be the primary key

Is this not correct?

thanks

Ian

[June7]

Okay, that is true. In this case it is the combined values of Plot and GraveNo (and Mlink ?) that make the records unique. I avoid compound keys. So converting to the GraveID as PK/FK is reasonable.

You still might want to set the Plot and GraveNo fields as compound index to avoid duplicate pairs. What is Mlink?

[Jen0dorf]

Hi

Mlink was setup in the original database to link to the memorial table where the memorials are listed. However an inspection of the memorial table show me that memorials can be linked to other relatives buried in different plots.

All becoming a bit more complicated than I imagined ;-)

Off to look up compound indexs

thanks

Ian

[June7]

Oh, then to get the new GraveID FK field populated, will need those 3 fields (Plot, GraveNo, Mlink - or maybe not Mlink, still not sure where this belongs) in both tblGraves and tblDeceased and link on all 3 (or 2) in the UPDATE action. Once the new GraveID FK field is populated, can delete those fields from tblDeceased.

[Jen0dorf]

Hi

thanks again, think I begin to see the light!

I'll have an experiment ( with a backup)

Ian

[Jen0dorf]

Hi

back again - a bit wiser and of course a total Expert - I think not;-)

Done some reading and puzzling etc and have grasped some of the thought processes

I understand this code and the principle behind it
INSERT INTO tblGraves(Plot, GraveNo, Mlink) SELECT DISTINCT Plot, GraveNo, Mlink FROM oldtable;

However at the risk of being totally foolish how do I implement this code.

I thought I would find a non wizard box to type the code in manually but I can't.

Also there seems to be a lack of information on SELECT DISTINCT I'm assuming this means that the combinations of plot, mlink and GraveNo has to be unique?

thanks a lot

Ian

[June7]

Switch query builder to SQL View to type statement.

[Jen0dorf]

So simple when you know

thanks

Ian

[Jen0dorf]

HI

Still trying to update one field with a value from another table. Had lots of help and have I think grasped the concept.

used the following code

UPDATE tblDeceased, tblgrave SET fk_GraveID = tblGrave.GraveID
WHERE tblDeceased.GraveNo=tblGrave.GraveNo AND tblDeceased.Plot=tblGrave.Plot;

Iam using two tables tblGrave and tbldeceased.

My aim is to update the foreign key field in tbldeceased with the Primary key in the table tblGraves, Primary key is GraveID and foreign key id fk_graveID

criteria must be that the combination of plot and Graveno must be valid

Using the code posted on the forum I ran the update.

To my horror the field fk_GraveId is being updated with the data from the plot field which is formated as text. Both the GraveId and fk_Grave Id are number fields.

Would really appreciate a pointer in the right direction

thanks

Ian

[June7]

Your SQL looks okay, although could try INNER JOIN to link the related records instead of the WHERE. First build the query as simple SELECT with INNER JOIN then click UPDATE from the ribbon and set the UPDATE TO row under the fk_GraveID field.

UPDATE tblDeceased INNER JOIN tblGrave ON tblDeceased.GraveNo=tblGrave.GraveNo AND tblDeceased.Plot=tblGrave.Plot SET fk_GraveID = tblGrave.GraveID;

GraveID is an autonumber type field? If you want to provide db for analysis, follow instructions at bottom of my post.