Query to find customer by phone number (easy answer for experienced SQL user)

[dipique]

I picked up Access a few weeks ago and have gotten pretty good at basic queries and VBA. However, I've run into one that's beyond my ability to figure out and I'm having trouble describing well enough to google it. Here's my situation:

I have two tables.

1. An export of all my iPhone texts (tblSMS).
It has the phone number, the timestamp, and the text message.

Phone Number	Timestamp	Text Message
8131111112	4/7/2012	Yo, how's it going?
8131111113	4/7/2012	All the clouds are falling :-/
8131111112	4/8/2012	Yeah, me too.
8131111114	4/9/2012	Dude this is a wrong number.

2. An export of all my iPhone contacts (tblContacts).
It has the Name and up to three phone numbers.

Name	Phone 1	Phone 2	Phone 3
Bill Davis	8131111113	8139999991
Johny Depp	8139999992	8139999993	8131111112
Corey Epstein	8139999994		8139999996

I would like to create a query with all the fields in table 1 PLUS the contact with a matching phone number. So the query result would be:

Phone Number	Timestamp	Text Message	Contact
8131111112	4/7/2012	Yo, how's it going?	Johny Depp
8131111113	4/7/2012	All the clouds are falling :-/	Bill Davis
8131111112	4/8/2012	Yeah, me too.	Johny Depp
8131111114	4/9/2012	Dude this is a wrong number.	Not Found

What does this query look like?

Thank you,

Dan

[orange]

I think the issue is with your Table 2 . What differentiates Phone 1, Phone 2, and Phone 3?
If these are Cell, Home and Work phones(for example), then you could name them such and create query.

The key to successful queries is good table design.

There is a good article on design concepts here
http://forums.aspfree.com/microsoft-...es-208217.html

[dipique]

@Orange: Typically they are Cell/Phone/Work, but they might also be two cell phone numbers, etc. The bottom line is that I could find a match in any of the phone number fields, so for my purposes they are simply [Phone 1], [Phone 2], and [Phone 3] because I cannot treat them differently.

Is there any particular reason that would cause a problem?

Dan

[R_Badger]

Is table2 the output that you automatically get from your iphone when you download it?

If you dont fancy restructuring your tables (which is the ideal solution)
There are a couple of ways to do it,
#1 insert nested IIfs containing dlookups (long and messy)
#2 use a report to view it, and insert an onformat groupheader event which uses ifs to determine what to put there, (still messy)

Either way I'm just too plain lazy to type out the vb or nested IIF statements to do it, however if that's the way you want to go I'll see if i can point you in the right direction.

[orange]

No, but if the phones are just phones, then better to have a junction table like
ContactPhones
ContactPhoneId
ContactId (FK to Contact)
PhoneNumber(FK to Phones) These 2 FKs would form a unique index(compound)

It really depends on what you are trying to do. What do you want in the end.

You have Contacts, Phones and Messages

A Contact may have 1 or more phones(numbers)
A message involves 1 phone(number)
A message has a TimeStamp
A Message may be from 0 or 1 Contact(wrong number/new contact)


Contacts(ContactId,ContactName,ContactAddress,...)
Phones(PhoneNumber)
Messages(MsgId,PhoneNumber,TimeStamp,TextMessage)

You could work from a layout as attached.

If the Messages.PhoneNumber is not in your Phones table, then it is an unknown number(wrong number or new contact possibly).


OOOPs..I see others have responded while I was writing.


Here are some queries based on revised structure -- again it depends on what you want to do.

GetAllContactPhones
SELECT Contacts.ContactName, ContactPhones.PhoneId
FROM Contacts INNER JOIN ContactPhones ON Contacts.ContactId = ContactPhones.ContactId;

GetAllMessages
SELECT Messages.MsgId, Messages.PhoneNumber, Messages.MsgTimeStamp, Messages.TextMessage
FROM Phones INNER JOIN Messages ON Phones.PhoneNumber = Messages.PhoneNumber
WHERE (((Phones.PhoneNumber) Is Not Null))
UNION SELECT Messages.MsgId, Messages.PhoneNumber, Messages.MsgTimeStamp, Messages.TextMessage
FROM Phones Right JOIN Messages ON Phones.PhoneNumber = Messages.PhoneNumber
WHERE (((Phones.PhoneNumber) Is Null));

GetAllMessagesWithContactNames
SELECT GetAllContactPhones.ContactName, GetAllMessages.MsgId,
GetAllMessages.PhoneNumber, GetAllMessages.MsgTimeStamp,
GetAllMessages.TextMessage
FROM GetAllContactPhones RIGHT JOIN GetAllMessages
ON GetAllContactPhones.PhoneId = GetAllMessages.PhoneNumber;

[dipique]

I see what you're saying. I suppose I can just make a table with each phone number, phone number type, and a reference to the contact to which it belongs.

I was hoping I'd have an easy fix inside a query (i.e. "WHERE [Phone Number]=[Phone 1] OR [Phone Number]=[Phone 2] OR [Phone Number]=[Phone 3]").

Thanks for the assistance, I think I know which direction to go now.

Dan