Parameter Query (1 Viewer)

[cobrajetltd]

Hi all, I have a problem that I have no clue on how to figure it out.. first off, here is the problem:

1.) The area code has been changing for various visitors throughout the country. Sonoma Farms would like to keep their visitor data accurate, so they have asked for a parameter query that will automatically update the area codes when they change. The user will enter the old area code, the exchanges within that area code that will change, and the new area code. Given these parameters, the tblVisitors table phone number should be updated. Create a query to accomplish these tasks. Save the query as qryUpdatePhone.

2.) When you have the query complete, test the query with the following conditions:

Area code 701 with exchanges 555 and 935 will change to area code 812.

Area code 555 with exchanges 899 and 277 will change to area code 807.


I know I have to extract the area code & prefix from VisitorPhone in tblVisitors, but I do not know how to do that.. 2nd, the format for the phone number is xxx xxx-xxxx

Any help would be appreciated!! Thanks!
Tina

 

[Pat Hartman]

Sounds like a homework assignment to me. However, the easiest way to do this is to create a table named tblAreaCodeChanges with three columns -
OldAreaCode
Exchange
NewAreaCode

Add the change information to the table.

Create a query using the QBE grid. Add tblVisitors to the grid, then add tblAreaCodeChanges. Draw join lines connecting the AreaCode to the OldAreaCode and the Exchange to the Exchange (this is a much more difficult problem if the parts of the telephone number are not contained in separate columns).

Click on Query in the menu and change the query type to update.

Select the AreaCode column. In the UpdateTo cell, type tblAreaCodeChanges.NewAreaCode