Link to home
Start Free TrialLog in
Avatar of Amour22015

asked on

MS Access - Case/Query/Syntax

Hi Experts,

Looking for the best way to handle this:

I have one table where phone numbers are in.

"Ams_Phone" Table

In that table there are 7 different Ams_Phone.phonetypes:

34 = Work
35 = home
36 = Mobile
43 = Fax
67 = Alternative Phone 2
68 = Alternative Phone 1
69 = Alternative Phone 3

And of course: = phone Number.

The column PhoneType is only a number field.

I am going to be placing the results in a different table:
tblNAsCommunication.Purpose and tblNAsCommunication.IsKeyPhone = '1'

So if in "Ams_Phone" Table where Ams_Phone.PhoneType = 34
tblNAsCommunication.Purpose = Work And tblNAsCommunication.IsKeyPhone = "1"

I need to setup a query syntax to do this?

Thanks for any help.
Avatar of Robert Sherman
Robert Sherman
Flag of United States of America image

Does table tblNAsCommunication already exist, or is this something you want to create as a result of the query?  If it already exists, does it have a field that can be used to relate the two tables together (for example, Phone Number in both tables)

Assuming Ams_Phone has the phone number and PhoneType for each phone, generating a make-table query or an update-table query shouldn't be too complicated.  

One piece of information I don't see in your question is where the "isKeyPhone" will get its data from..
Avatar of Kanti Prasad
Kanti Prasad


you can look into the below link and frame your sql

below is a sample tnc is an alias for  tblNAsCommunication ap is the alias name rfor Ams_Phone

INSERT INTO tblNAsCommunication tnc
SELECT ap.phonetypes FROM Ams_Phone ap where ap.phonetypes = 34;
Avatar of Amour22015


Maybe this will clear things up:

This is a conversion setup.

so and old table:
Ams_Phone Table

is going into a new table:

So yes table tblNAsCommunication already exists

The phone numbers from: are being placed into tblNAsCommunication.Entry
tblNAsCommunication .IsKeyPhone is generated by my query and will have a "1" when it is the key.

So if in the Ams_Phone table
Where Phone, PhoneType = 34 then:
tblNAsCommunication.Entry =
tblNAsCommunication .Purpose = "Work"
tblNAsCommunication .IsKeyPhone = "1"

Thanks for helping me.
sorry it should phonetypesvalue  replace it with the column name where you have the 'work' stored

INSERT INTO tblNAsCommunication tnc
SELECT ap.phonetypesvalue  FROM Ams_Phone ap where ap.phonetypes = 34;

I assumed you have in Ams_Phone table
phone       phonetypes   phonetypesvalue
12345              34                      work   ........
 37585             35                      home

tblNAsCommunication tqable
purpose    IsKeyPhone .......
This is mentioned:
SELECT ap.phonetypesvalue  FROM Ams_Phone ap where ap.phonetypes = 34;

But ap.phonetype changes.

So Ap.PhoneType =


So does this not have to be in some kind of Case statement?
ap.phonetype = 34
tnc.Purpose = "Work"
tnc.IsKeyPhone = 1
ap.phoneType = 35
tnc.Purpose = "Home"
tnc.IsKeyPhone = 1
ap.phoneType = 36
tnc.Purpose = "Mobile"
tnc.IsKeyPhone = 1
so on.....

Please help and thanks
But the Case statement would only work in VBA and I am dealing with Query for Access.

So something like:

IIf([ap].[phonetype] = 34
 [tnc].[Purpose] = "Work"
 [tnc].[IsKeyPhone] = 1)

Please help and thanks
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have to say using the switch statement is the best way to do this.  Great Job