Connecting Data from Parent Table into Table

[Denise2020]

Good day, all!

I am having a hard time even forming a title for this topic. I was given an excel sheet and asked to create a simple inventory database of it. I cleaned it up and imported it but want to make a parent table of an inventory location.

The parent table contains:
StoragelocID
StoragelocName (not actual names used)

The table contains:
StoragelocName imported as Short Text

I added a column called StoragelocID (field type number) and set the relationship between the table and parent table StoragelocIDs. The problem is that the table.StoragelocID column is still empty because it isn't connecting the old Short Text Name column to the new number ID column. How would I go about connecting the two so I can delete the old name/short text column? I hope you can figure out what I am trying to say because my explanation is dismal - many apologies!

I don't know if this would be called a union, a merge, a join, but basically the relationship is Parenttable.StoragelocID = table.storagelocID and somehow I need to get the data into the new and proper column. I tried an update query to set StoragelocName = StoragelocID but unfortunately the field type mismatch error prevented it. Quite possibly I did it wrong?

Thanks as always!

 

[Ranman256]

If StorageLocName is unique,then there is no need for the ID field.
Name would be the key to prevent duplicates. Is this true? Or is this just an example table?

If keyed, then the master form can link to the subForm via this key.

 

[Denise2020]

I did think about using StoragelocName as the ID field. I am a completely self-learning Access user so my knowledge comes from googling and watching online. Anyway, I googled it and it seemed frowned upon so I let the autonumber remain. Would love to remove any unnecessary fields though if that is not the case.

So if I were to do that, I could just change the name field to the Primary Key, leave both as short text, and problem solved? The relationship would automatically make that work, right?

 

[gemma-the-husky]

The parent table contains:
StoragelocID as long
StoragelocName (not actual names used)

The table contains:
StoragelocName imported as Short Text
StoragelocID as long

You need a new numeric field in the child table to match the one in the parent (I assumed they are both long numbers)
Then you join the two tables on the text field, and update the location id in the child table. Then you can delete the name field in the child table.
That's what I would do. This way, you can rename the locations in the parent table, without needing a cascading update.

 

[Denise2020]

My apologies but the field type for the StoragelocID on the parent table is actually autonumber. Does that change things, Dave?

 

[arnelgp]

if the "table" is the inventory items with Location Names,
then you need an Insert Query Into the "parent" location:
but first remove all the records from [parent]:

Insert Into [parent] (StorageLocName) select Distinct StorageLocName from ["table"]


now, add a FK field to the "table" (StorageLocID, long).

after create an Update query to update StorageLocID of "table" with "parent" StorageLocID:

Update "table" As A Inner Join [parent] As B On A.StorageLocName = B.StorageLocName Set A.StorageLocID = B.StorageLocID;

 

[gemma-the-husky]

@arnelgp just described a different way. Personally I would take a copy of the parent table before deleting it, just in case.

In answer to your other question, no - you want the parentid to be an autonumber - otherwise you have to either manually decide on the next number, or create a new number by adding one to the highest number. An autonumber is convenient. Bear in mind that an autonumber won't guarantee an intact sequence, though. The you want the matching field in the child table to store the same value, but just as a number. So then you can have many items in each storage location.

 

[Denise2020]

Thank you SO much to all of you! I got it working!

Have a great evening!