Create mailing list using if then logic? Mail to facility or physician's office.

[Slush]

I need to create a mailing list to physicians. Physician's can have multiple addresses. Supplemental physician addresses are stored as facilities in the facility table. Each physician's main address is stored in the physician table. (I did not design this, but I have to work with it).



In the physician table there are 2 key fields: Notify_Fac and FacID. If Notify_Fac=yes the letter must be mailed to the facility's address (the value in the FacID field indicates which facility address. Facility addresses are stored in the facility table). If Notify_Fac=no then the letter should be mailed to the physician's main address which is stored in the physician table.

I understand how to do this building 2 separate queries, but I need to create one mailing list I can use for running a mail merge. How do I accomplish this? I am not sure how to write if then logic in Access or if there is a better way.

Thanks in advance!

[CJ_London]

1. Join the two tables with a left join between the physicians table and the facilities table (on the FacID field and physician ID field perhaps?).
2. in columns in the query you would have something like

MailAddr1:iif(Notity_Fac,tblPhysicians.Addr1, tblFacilities.Addr1)
MailAddr2:iif(Notity_Fac,tblPhysicians.Addr2, tblFacilities.Addr2)
etc

Going forward, I would update all addresses in tblPhysicians to tblFacilities, then you don't need the Notify_Fac field either, once copied over, update the FacID to the new ID for the address where Notify_Fac is true. You might need an additional column to indicate the 'primary address'

[Slush]

Thanks Ajax. I should have provided more detail in the original post. Below is a screen shot of the tables. DMS Person holds the physician information with their main address. Facility holds additional office locations. The Link CTC Person table links patient information to their physicians or the facility if the physician has multiple office locations. (There is another table not shown with more patient information).

Users will manually review physicians with multiple addresses and decide for each patient if the letter should be mailed to the physician's main address or facility address. If they select to notify the MD at a facility, "Notify FAC" will =yes and FAC ID DMS in the Linking table will have a the facility ID in it (not null).

In your example, I am unclear what the field Addr1 and Addr2 refer to.

Considering the additional information what do you suggest.

[CJ_London]

In your example, I am unclear what the field Addr1 and Addr2 refer to.

you said you needed a mailing address, could just as easily have said street, town, county

[Slush]

Got it. Thanks!!