Append Query-Specific How to (1 Viewer)

Toolman

Access Newbie
Local time
Today, 02:41
Joined
Jul 25, 2009
Messages
28
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
 

Attachments

  • Relationships.JPG
    Relationships.JPG
    32.6 KB · Views: 78

ajetrumpet

Banned
Local time
Today, 01:41
Joined
Jun 22, 2007
Messages
5,638
backup your table before doing this...

add a field to your referral table first: SpecialtyID.

then run this in the qry design:
PHP:
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:
PHP:
docmd.setwarnings false
   "UPDATE referral SET
      [SpecialtyID] = " & dlookup("SpecialtyID", "Doctor", "[ID] = " & [DrID])
docmd.setwarnings true
VBA 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.
 

Toolman

Access Newbie
Local time
Today, 02:41
Joined
Jul 25, 2009
Messages
28
Sorry for taking so long to respond.

Thank you. I will try to set this up.
 

ajetrumpet

Banned
Local time
Today, 01:41
Joined
Jun 22, 2007
Messages
5,638
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)
 

Users who are viewing this thread

Top Bottom