Displaying data from 2 tables in one

[kbremner]

First a little background...



My account table/form has two pertinent fields: AcctName & AcctPhone.

For residential accounts, the AcctName is the household name and AcctPhone is the house or primary phone number. For commercial accounts, the AcctName is the name of the company and AcctPhone is the number for the main office.

On this form, there is also a subform to list "Contacts". This table includes ContactName, ContactPhone, ContactTitle. For residential accounts this would include alternate phone numbers such as mobile phones, work phones, etc. For commercial accounts it would include people within the company and there phone numbers.

The tricky part is on my "Phonebook" form. I want this form to list all of the AcctName listings with corresponding phone numbers AND all of the ContactName listings. I am hoping to have them all seamlessly listed together.

Example

AcctName: My Company
AcctPhone: (000) 555-1234

---ContactName: Bob Robertson
---ContactPhone: (000) 555-4321
---ContactTitle: Account Manager

---ContactName: Rob Bobertson
---ContactPhone: (000) 555-9876
---ContactTitle: Facilities Manager

Then on my Phonebook form you would see the following

Bobertson, Rob_____(000) 555-9876_____Facilities Manager
My Company_______(000) 555-1234_____
Robertson, Bob_____(000) 555-4321_____ Account Manager

Everything should be listed together in alphabetical order. There does not need to be any reference from contacts to company. Users will be able to double-click a name, either company or contact and open the corresponding account page.

What I have for the rowsource right now is:

(Client = AcctName; Phone1 = AcctPhone)

SELECT tblClient.ClientID, tblClients.Client, tblClients.Phone1 FROM tblClients WHERE (((tblClients.Client) Like forms!frmContactList!txtLastNameFilter & "*")) ORDER BY tblClients.Client;

This successfully displays the AcctName from the main table/form, but I don't know how to go about merging the two into one list.

[weekend00]

you must have reference from contacts to company, or how do you know a contact belongs to which company?

for example: user double click on name "Rob Bobertson", how do you know ""Rob Robertson" is in some company with him, and which company is he in?

[kbremner]

There is a relationship between the two tables... tblClients.ClientID and tblContacts.ClientID

[weekend00]

if you want that:
Users will be able to double-click a name, either company or contact and open the corresponding account page.

You need to make sure the name of contacts are unique, but this is not a good idea.


Except this problem, you may use union query to get all records.

[kbremner]

The union query was what I needed for the list using the following statement:

SELECT ClientID, Client, Phone1, email from tblClients UNION select ContactID,LastName + ', ' + FirstName AS Contact, Phone, email from tblContacts ORDER BY Client;

Only issue I'm having right now, somewhat unrelated to the original questions, how do I get the phone number to output
in format " (###) ###-####

I have input fields in both tables but the output from the query is just a number?

[weekend00]

what is the phone number in you table right now? a long number or text of "0" - "9", or text of "0" - "9" and "-"?

if it's a number or text of of "0" - "9", you can use expression:
phone: format(phoneNumber,"(###)###-####")

[kbremner]

It is a text field. Problem is, I don't know how to add formatting to the sql statement.

Also, on the original issue, with my new combined list, If i click on any of the entries that are from the contacts table (the many side of the relationship) the focus goes immediately to the first record listed from the Client table. Any ideas why it would do this??