Hello everyone,

I'm very new to Access and I think I have to make a major change to a table. I have attached a pic of my relationships. I think I need to add the SpecialtyID field (Specialty table) to the Referrals table.

I know to use an Append Query. But I'm unsure how. There are over 1000 doctors to apply the SpecialtyID to in amost 7000 records. I want to make sure the correct SpecialtyID matches up the the correct DrID.

I could really use some help.

Thanks,
Don

backup your table before doing this...

add a field to your referral table first: SpecialtyID.

then run this in the qry design:UPDATE referral SET
[SpecialtyID] = dlookup("SpecialtyID", "Doctor", "[ID] = " & [DrID])if that doesn't work in the qry grid window, try it in VBA functions instead:
docmd.setwarnings false
"UPDATE referral SET
[SpecialtyID] = " & dlookup("SpecialtyID", "Doctor", "[ID] = " & [DrID])
docmd.setwarnings trueVBA will give you more headaches, as the syntax is never uniform with stuff like this, especially with weaving in and out of quotation mark blocks like this.

Sorry for taking so long to respond.

Thank you. I will try to set this up.

Sorry for taking so long to respond.

Thank you. I will try to set this up.

hey man....we don't care how long you take to respond. it's your problem to fix, not ours. :D you're not working for me (i hope)