How to add a PK from one table to the FK in another table with specific criteria

[mgmercurio]

Hello all ! First I want to say thank you in advance from all the experts here. I am a novice Access user and don't really know what I am doing...but can stumble my way through...

Anyway, I have two very large tables in my database:

tbl_Accounts
AccountID (PK)
AccountName (Short Text) joined in a 1 to many (left to right) relationship with AccountName in tbl_Contacts
AccountPhone (ShortText)
AccountFax(Short Text)
AccountBillingStreet1 (Short Text)
AccountBillingStreet2 (Short Text)
AccountCity (Short Text)
AccountState (Short Text)
AccountZipcode (Short Text)

tbl_Contacts
AccountContactID (PK) Autonumber
AccountID (FK) number
AccountName (shorttext) joined in a 1 to many relationship with AccountName in tbl_Accounts
FirstName (shorttext)
LastName (shorttext)
OfficePhone (shorttext)
MobilePhone (shorttext)
Email (shorttext)

I have already imported the data into both tables from spreadsheets and tbl_account table holds 2200 records. tbl_Contacts holds 8800 records
The reason the contacts table hold more records is because, for many of the accounts; there are many contacts specific to that account. In some cases, I could have a 100 contacts for one specific account. Two more pieces of info before I ask my question...

1. In each of these tables, the AccountName field holds the exact same name. However, in the Contacts table, there is multiple records with the same accountname. (due to some accounts have multiple contacts)
2. The AccountID in the tbl_Accounts table is sequentially number from 1 to 2200 due to it being the primary key set to autonumber. This happened during the import process. However, the AccountID (FK) in tblContacts is left blank.

Now for my question....
I am wanting to do a query to populate the AccountID field in the Contacts table with the exact same AccountID in the Accounts table, but with one condition...
I need the AccountID from the Accounts table to be the same ID in the AccountID of the Contacts table in the records that have the same AccountName.

Look at the attached screenshots. You will see in the Contacts table, noted in red the duplicated AccountNames listed because there is more than 1 contact for that account. You will also see in the second screenshot (Accounts Table) noted in Blue...the AccountID automatically generated when doing the import.

NOTE - I manually typed in the AccountID into the Contact Table in the field you see in the screenshot. I did this just for the purpose of example. Normally those records as well as all the records in the Contacts Table in the AccountID field are blank.

So again my question - how do I write a query to populate the AccountID in the Contacts table with the AccountID from the Accounts Table using the respective ID number from the AccountName?

Finally, please remember...I am not a DB admin nor a programmer or even very good with SQL...so any advice with very simple (treat me like Im 10 years old and dont know anything about Access.... heh heh ) would be very very appreciated as I have been battling this for days now.

Thanks!
-mgm

 

[Minty]

If your AccountName is unique then this is pretty simple.
But is it?

Have you tried simply creating a query with both those table in and joining them on the Account Name?

 

[mgmercurio]

If your AccountName is unique then this is pretty simple.
But is it?

Have you tried simply creating a query with both those table in and joining them on the Account Name?

Not sure Im understanding what is meant by "Is your account names unique..." Each Account has a completely different name if that is what you are asking.

As far as the query goes...no..I have not done that...can you elaborate?

Thanks!

 

[plog]

To do this right requires some validation--before and after the operation. Here's what you need to do:

1. Make a copy of your database. Whenever you want to UPDATE data always always always make a backup in case things go sideways.

2. Make tbl_Accounts contain unique AccountName values. If you have 2 records in tbl_Accounts with the name "Acme" but they each have different ID numbers, then you don't know which ID will go into tbl_Contacts. This query will identify duplicates:

SELECT tbl_Accounts.AccountName, Count(tbl_Accounts.AccountID) AS CountOfAccountID
FROM tbl_Accounts
GROUP BY tbl_Accounts.AccountName
HAVING (((Count(tbl_Accounts.AccountID))>1));

You need to open up both tbl_Accounts and tbl_Contacts filtered to just those AccountNames and either manually enter the correct ID into tbl_Contacts or delete duplicate records from tbl_Accounts. You need to ensure that when you match on AccountName only 1 match can be made.

3. Run an UPDATE query to populate AccountID in tbl_Contacts. This query will do that:

UPDATE tbl_Accounts INNER JOIN tbl_Contacts ON tbl_Accounts.AccountName = tbl_Contacts.AccountName SET tbl_Contacts.AccountID = [tbl_Accounts].[AccountID]
WHERE (((tbl_Contacts.AccountID) Is Null));

4. Find any numatched AccountNames in tbl_Contacts. This query will do that:

SELECT tbl_Contacts.*
FROM tbl_Contacts
WHERE (((tbl_Contacts.AccountID) Is Null));

That lists all the contacts without records in tbl_Accounts. You will need find the data and manually add records to tbl_Accounts for those AccountNames.

 

[mgmercurio]

Thank you very much for this! I will do as instructed and report back..... Stand by.

p.s. YES!! Backup Backup Backup

 

[mgmercurio]

Plog...I can't thank you enough for what you did here today. Everything is working fine. WOOHOO!!!!

You saved me from hours and hours of trial an error and quite possibly a brain hemorrhage from all the blood vessels popping out from anxiety!!!

Seriously...thank you very much. Best thing that has happen to me so far in 2021!

-mgm

 

[gemma-the-husky]

AccountID (FK) number
AccountName (shorttext) joined in a 1 to many relationship with AccountName in tbl_Accounts

you would be better not storing the account name in the contacts table. Use the AccountId, delete the account name, and establish the relationship based on the AccountID.

Depends how far you have got, as it might require a good number of changes.

 

[mgmercurio]

you would be better not storing the account name in the contacts table. Use the AccountId, delete the account name, and establish the relationship based on the AccountID.

Depends how far you have got, as it might require a good number of changes.

I did change it to AccountID