Adding PK as FK to table

[maw230]

How do I get get my PK from tblAlerts to be a FK in tblProducts?

I want the Alert_ID from tblAlerts to automatically populate as Alert_ID foreign key in tblProducts.

I've done this before, but I can't re-wrap my head around it.

You can't add data to tblProducts without adding it to tblAlerts first. And there are no entries in tblAlerts that won't have related entries in tblProducts.

So, when records are added to tblAlerts it should update Alert_ID in both tables. This is the Cascade update/delete property right?

The problem I'm having is adding the initial data to my tables... I add the data to tblAlerts then create the AutoNumber PK Alert_ID. Then add the data to tblProducts and add Alert_ID as a FK, but it doesn't auto-populate.

I think I'm missing something important here.

 

[boblarson]

The answer is tblAlerts is the table that a main form is based on and tblProducts is a table that would be used as the record source of a subform and the lnk would be the Alert_ID.

 

[maw230]

Ok, I think I understand. I would still be able to add the initial data with an Append query right? As in, I would not have to add the records through the form/subform.

 

[boblarson]

Ok, I think I understand. I would still be able to add the initial data with an Append query right? As in, I would not have to add the records through the form/subform.

I guess but it won't work as cleanly as a form/subform. With that, Access does all of the work for you including putting the foreign key in. How do you propose to do that with an append query???


EDIT: I'm constantly amazed at lengths people will go to in order to make themselves have to code or do everything that Access provides for free. Just boggles my mind.

 

[maw230]

I guess but it won't work as cleanly as a form/subform. With that, Access does all of the work for you including putting the foreign key in. How do you propose to do that with an append query???


EDIT: I'm constantly amazed at lengths people will go to in order to make themselves have to code or do everything that Access provides for free. Just boggles my mind.

I'm sorry I'm not familiar with adding more than one record at a time through a form/subform.

 

[boblarson]

Well, adding in data using an append query means there is a bit of a challenge here. You need to know the PK in order to add to the child table, correct? So, doing a bulk insert of values into the main table will work but you will still have to do something to associate the child records with them. You could iterate through code and add one alert and then any associated Products at the same time by using DAO and using the @@Identity to return the Primary key so you can use it as the foreign key.

Here's a bit of a sample of some code where I do this. I append first the parent record and then run an append query to do the child records.

            strSQL = "INSERT INTO tblKeyCharScenarios ( ScenarioName, ScenarioDate, UserID ) " & _
                     "VALUES (" & Chr(34) & strScenarioName & Chr(34) & ",#" & Date & "# ," & Chr(34) & GetUser() & Chr(34) & ");"
            CurrentDb.Execute strSQL, dbFailOnError
            Set rst = CurrentDb.OpenRecordset("Select @@IDENTITY As LastID")
            lngIdentity = rst!LastID
            LastScenarioID = lngIdentity

I use LastScenarioID as a property so I can then a bit later in my append query for my details, add that in.

 

[Lightwave]

Maybe a bit patronising but..

Remember (traditionally) you set up the one to many relationships in the relationship manager first and you generally enter all the information through the form in order for the standard population inheretance of the parent PKID into is FKID field of the child records. Just entering the information in the background tables of two related tables will usually result in either warnings or blank links..

In situations where I want to do one off big imports of information (typically when setting up a new system) I often manipulate the information using spreadsheets having carefully manipulated the information to ensure that what is to be imported information will comply with the specified relationships then import the Parent first and the child records second. Enforcing referential integrity will mean that any duff child records will be split into a paste errors table (Access 2003). You can do the kind of matching that Bob is talking about using lookups in excel as between any data tables there must be fields that allow you to match (even if its multiple fields) between tow table files before large scale matching happens...

Otherwise its a unsolvable puzzle that should have been matched as the information came in.

The Rosetta stone comes to mind

 

[maw230]

In situations where I want to do one off big imports of information (typically when setting up a new system) I often manipulate the information using spreadsheets having carefully manipulated the information to ensure that what is to be imported information will comply with the specified relationships then import the Parent first and the child records second.

So, organize my data in Excel --> Set up tables and relationships in Access --> import into Parent --> import into child.

When I am importing, how can I select which columns to append? If my memory serves, it is all or nothing in Excel '07. Unless I choose to import into a new table. Would that be the better option? Creating my autonumber PK in Excel beforehand... wait you can't convert pre-existing data into an Autonumber PK can you?

 

[boblarson]

When I am importing, how can I select with columns to append? If my memory serves, it is all or nothing in Excel '07. Unless I choose to import into a new table. Would that be the better option?

You might import to a transitional table and then append to the actual table from there. If possible I would create the transitional table in Access first and then import - don't import to a new table as that can have your datatypes go wacky.

Creating my autonumber PK in Excel beforehand... wait you can't convert pre-existing data into an Autonumber PK can you?

You can, but only if any of the numbers being appended don't exist in the table.

 

[maw230]

I imported all data into a new table, set up my relationships (1:m from tblAlerts to tblProducts on PK/FK Alert_ID), appended to tblAlerts (parent), and then on my attempt to append to tblProducts I got a key violation.

 

[boblarson]

I imported all data into a new table, set up my relationships (1:m from tblAlerts to tblProducts on PK/FK Alert_ID), appended to tblAlerts (parent), and then on my attempt to append to tblProducts I got a key violation.

Remember, with the Products you do NOT want to include its autonumber field in the append query. You are appending the FK which, while it is the autonumber in the tblAlerts table, it is not (or should not be) the autonumber in that table. Also, make sure that all datatypes are correct and that you aren't trying to insert nulls where nulls are not allowed nor empty strings where empty strings are set to not be allowed (if they are set that way at all).

 

[maw230]

You are appending the FK

Thanks, Bob. I was acting under the assumption that Alert_ID would be added to tblProducts automatically and that I did not need to actually append it manually... So, I just made a quick join and appended Alert_ID from tblAlerts to tblProducts and we are in business!

 

[Lightwave]

Glad you got it sorted Maw