Access newbie needing Help with Access table lookups

[Guest]

I am a new Access 2003 user and I am struggling with one thing to complete a
simple database. I have built an Events Timeline Database that is supposed
to document certain types of events over a certain period of time and create
a report to show this timeline of events. I am having trouble understanding
how to do a table lookup on The Events entry form to find a first name and
last name that matches a phone number on the Contacts table and then pull
that data into a data entry form. This query would also be used on the report
I am assuming so that the names would appear on the report.

Here is more background:

The database consists of two main tables which are called Events and Contacts

On the Case Event Entry Form, when a user enters a phone number on the
Events entry form and tabs off the field, I wanted to do a lookup (query?)
from the Contacts table that looks at all the 6 phone number fields on a
Contact record and pulls back the contact’s First and Last name that matches
the phone number to display on the form. I guess it would also need some
error handling if no number is found that matches a contact like putting
“unknown†in the field.

I want to pretty much do the same thing for a report on the Events Table
that shows the events over time. I am guessing that the report would again do
the same lookup (query?) for the First and Last names from the Contacts table
to match phone numbers from the events entry table and translates them into
the correct contact names printed on the report. This needs to be done
because we are constantly adding people and phone numbers to the Contacts
table so that when an event is originally entered we might not have a
matching phone number in the Contacts database. However, later we might.

I need some help on doing these lookups or the SQL statements. I am just not
sure how to do this. Any help, guidance or even a reference to someone that
could help me would be greatly appreciated.

 

[Duane Hookom]

You can normalize your Contacts table with a union query like:
SELECT FirstName, LastName, PhoneA As PhoneNumber
FROM Contacts
UNION
SELECT FirstName, LastName, PhoneB
FROM Contacts
WHERE PhoneB Is Not Null
UNION
SELECT FirstName, LastName, PhoneC
FROM Contacts
WHERE PhoneC Is Not Null
UNION
SELECT FirstName, LastName, PhoneD
FROM Contacts
WHERE PhoneD Is Not Null
UNION
SELECT FirstName, LastName, PhoneE
FROM Contacts
WHERE PhoneE Is Not Null
UNION
SELECT FirstName, LastName, PhoneF
FROM Contacts
WHERE PhoneF Is Not Null;

You can then use a combo box to select a phone number. You can also add this
query into your reports record source and join the PhoneNumber fields to
display the FirstName and LastName.