How do I make an "Option" Query

[Jamie Longmire]

Hi,

I am a bit of a newby at Access and I am building a database whereby I can
identify the phone numbers on my phone bill with the actual number in my
contacts list.

I'd like the query to display records (Name and Number) from the Contacts
table that best match the number in the PhoneBill table
I know this is a simple query but I have an issue. The data in the number
field in the PhoneBill table can be stored in different formats e.g.
+61408123456 or +61 408 123 456 or 0408 123 456. The data in the number
field in the Contacts table can be stored like +61 (408) 123456 or +61 (408)
123-456

I have been playing around with this query to identify only the three last
digits in the number field but no luck.

I have two tables with respective fields:
1. PhoneBill
Date
Time
Number
Duration
2. Contacts.
Name
Number

Any input appreciated

Regards,
Jamie

 

[Noozer]

I'd like the query to display records (Name and Number) from the Contacts

table that best match the number in the PhoneBill table
I know this is a simple query but I have an issue. The data in the number
field in the PhoneBill table can be stored in different formats e.g.
+61408123456 or +61 408 123 456 or 0408 123 456. The data in the number
field in the Contacts table can be stored like +61 (408) 123456 or +61 (408)
123-456

If you are storing one type of data without a standard format you are just
shooting yourself in the foot.

Decide on a single way to represent the phone numbers and then fix the data
in your database to match.

Make sure your form doesn't allow any format to be stored other than the one
you've decided on.

 

[John Vinson]

I'd like the query to display records (Name and Number) from the Contacts
table that best match the number in the PhoneBill table
I know this is a simple query but I have an issue. The data in the number
field in the PhoneBill table can be stored in different formats e.g.
+61408123456 or +61 408 123 456 or 0408 123 456. The data in the number
field in the Contacts table can be stored like +61 (408) 123456 or +61 (408)
123-456

I'm with Noozer. Don't confuse data STORAGE with data PRESENTATION.

I'd store both numbers with just digits, 61408123456; or, probably
better if you're covering several nations, break it up into country
code 61, area code 408, and phone number 123456. They can easily be
concatenated for display purposes. But if you're trying to match
123-456 with 123456, Access won't do it because *they are different
text strings*.

John W. Vinson[MVP]