Change Primary key and update data?

[mcnpr]

Hello,

Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!

My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].

Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)

The db has been so successful that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).

So, I have:

tblPeople:
.FullName (Primary Key)
.InitialLastName

tblIdeasBank:
.Originator (stored as InitialLastName)


And I would like:

tblPeople:
.PersonID (Primary Key)
.FirstName
.LastName

tblIdeasBank:
.IdeaID (Primary Key)
.Originator (stored as PersonID)

With a One-Many relationship from PersonID to Originator

Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?

I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!

Thanks,

Nick

 

[RuralGuy]

This is just a one time operation so start a new table and then practice creating append and update queries that solve your problem. It is good that you are fixing it now rather than later.

 

[boblarson]

Also, I would HIGHLY suggest using the same name for your Foreign keys as your Primary Keys, it helps people coming along behind you to know which key is really the foreign key and which PK it matches with. For example, instead of Originator, you should just use PersonID in the applicable tables since you used that as the PK in the tblPeople table.

 

[Banana]

If I can just ride on this topic and ask a similar question...

In some of my tables, I have a "placeholder" record to handle exceptions. For example in my tblPerson, I have (Unspecified) with default values filled in so my users can use in such event they have no information about the person they're describing. Now, some of placeholders were belatedly added, resulting in non-contingous set of IDs for grouping the placeholders. Is it possible to change the keys so I can group the placeholders separately from the real records for internal engines' dealing, perhaps by giving it negative numbers, or setting the auto-number at 100, and force ID to be less than 100 for new placeholders?

TIA.

 

[mcnpr]

Thanks guys for your help... I hadn't thought about update queries (mainly because I have not done much sql). And, therefore, I'm not very good at it! I understand the theory, but am having difficulty getting the correct syntax to work.

I have:

tblPeople.InitialLastName: J Bloggs, A Nother, P erson
tblPeople.PersonID: 1, 2, 3

and

tblIdeasBank.Originator: A Nother, P Erson, J Bloggs, P Erson

which I want to update to

tblIdeasBank.Originator: 2, 3, 1, 3

Etc.

Can you help me to understand how to do this (in very simple detail on the syntax please!!)

**********************
EDIT - I have figured out where I was going wrong! I was trying to put the WHERE clause in the Criteria box in the query design view.... have just switched to SQL view, and realised this meant WHERE was in there twice!! Doh! Sorry for wasting your time guys!
**********************

(And Bob, I take on board your comment about having the same name for Primary Key and Foreign Key, but I use the same foreign key 4 times in the same table: Originator, and up to 4 people responsible for implementing the idea... but perhaps I should rename them slightly... PersonID_Orig, PersonID_Resp1, etc)

Thanks again for your help!

Nick

 

[boblarson]

(And Bob, I take on board your comment about having the same name for Primary Key and Foreign Key, but I use the same foreign key 4 times in the same table: Originator, and up to 4 people responsible for implementing the idea... but perhaps I should rename them slightly... PersonID_Orig, PersonID_Resp1, etc)

If you have repeating fields in there, then your database is NOT designed properly and is not normalized. You would need a junction table for capturing the one-to-many situation that you are attempting to do.

 

[mcnpr]

Um... so I have an idea, created by Pete, with Joe and Dave responsible for implementing it.

I currently have tblPeople, where Pete = 1, Joe = 2, and Dave = 3 (Name = PersonID)

I would therefore have, in tblIdeasBank, for this idea, PersonID_Originator = 1 (i.e. Pete), PersonID_Resp1 = 2 (i.e. Joe), PersonID_Resp2 = 3 (i.e. Dave)

Is this wrong? If so, how should it be structured properly?

(Like I said, I am self taught... not the best way to learn the 'right' way to do things... any help is very much appreciated!)

Nick