DropDown with infos from indirectly linked tables

[Luc]

Hi ! Any help on the following problem in Access XP would be highly appreciated.

I have 3 tables with the following structures:

AddressTypes:
- RefField 'autonumbered field (primary key)
- Type 'used to store "Office" (as RefType=1) and Home (as RefType=2)

SendMailTo:
- RefField 'autonumbered field (primary key)
- AddressRef 'refers to a tbAddress.RefField (select list)
- AddressType 'refers to a tbAddressTypes.Type

Addresses:
- RefField 'autonumbered field (primary key)
- Name
- OfficeAddress
- HomeAddress

What I'd like to do is:

display the Addresses.OfficeAddress and Addresses.HomeAddress for the Addresses.RefField =
SentMailTo.AddressRef next to the corresponding "Office" and "Home" choices of the
AddressTypes.Type in the dropdown list of the SendMailTo.AddressType.

The dropdown list would then display as:

Type | Address
"Office" | Addresses.OfficeAddress.Value for Addresses.RefField = SentMailTo.AddressRef
"Home" | Addresses.HomeAddress.Value for the same record

Any solution would be highly appreciated. Please note that the OfficeAddress and
HomeAddress may be empty. Please e-mail to: (e-mail address removed). Thanks to everyone.

 

[Bob Miller]

Okay, I've got to ask why you are using three tables to do the work o
one?
I would have one table with:
- RefField 'autonumbered field (primary key)
- Name
- OfficeAddress
- HomeAddress
I really don't understand what you want from this data, however,
believe that this design will help you get to where you want to be.

 

[Luc]

Dear Bob:

I thank you for your reply. The reason why I use 3 tables is that they
contain other information that I have not included in the structures of the
tables reported below. For example, the table "SendMailTo" contains other
fields having nothing to do with addresses.

Also and more importantly, the selection of AddressType in the SendMailTo
table let me know if I must send mail using the OfficeAddress or the
HomeAddress. I don't want to store this info in the Addresses table because
the address to be used depends on independent conditions. And I do want to
store both OfficeAddress and HomeAddress in the same record for other
important reasons irrelevant to be detailed here.

Thus, my problem still remains the same. Any suggestions ?

Thanks.

Luc