Query all phone #s from different tables

[hockey8837]

Hi,
Thanks in advance for advice/help! I have two tables with phone numbers ("tblContacts" and "tblAltContacts"-which is family of the primary contact & associated with the "ContactID"), both of which have three fields of phone numbers ([Home], [Mobile], & [Business]).

I have a third table, "tblConversations," which collects/tracks conversation data, including phone # called for the conversation.

Basically I want to set up a field/combo box on the form for 'tblConversations' to query ALL phone #s in the database but also allow the user to input #s not in the DB (sometimes current numbers on record change, but I want to keep a record of old #s called or have the ability to input a # that I may not need to store on the Contact's permanent info.). I'd set up a Union Query to first get everything pulled together, but I can't input #s NOT already in the DB when I set my combo box to this query; here's my code:

SELECT qryContactsExtended.ID, qryContactsExtended.[Contact Name], IIf(IsNull([Home Phone]),IIf(IsNull([Mobile Phone]),[Business Phone],[Mobile Phone]),[Home Phone]) AS Phone
FROM qryContactsExtended

UNION ALL SELECT qryAlternativeContactsExtended.ContactIDFK, qryContactsExtended.[Contact Name], IIf(IsNull([qryAlternativeContactsExtended].[Home Phone]),IIf(IsNull([qryAlternativeContactsExtended].[Mobile Phone]),[qryAlternativeContactsExtended].[Business Phone],[qryAlternativeContactsExtended].[Mobile Phone]),[qryAlternativeContactsExtended].[Home Phone]) AS Phone
FROM qryContactsExtended LEFT JOIN qryAlternativeContactsExtended ON qryContactsExtended.ID = qryAlternativeContactsExtended.ContactIDFK;

(forgive the spaces in the field names, I started with an Access Contacts template and it is set up with them and I've been too lazy to go in and fix)

My goal for this was so the user wouldn't have to flip back and forth between tabs, copy, & paste to record which phone # they called. I'd tried to set it up originally so that only phone #s associated with the [ContactID] (or [AltContactID] who is also associated with the primary [ContactID]) would be available in the combo box, but ran into issues. I may be thinking about this wrong. Is this possible to do all this in the same cell?

Thanks

 

[jdraw]

Cells is an Excel term. Access has tables, records and fields.

Can you post a jpg of your tables and relationships?