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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
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..
Kanti PrasadCommented:

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;
Amour22015Author Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kanti PrasadCommented:
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 .......
Amour22015Author Commented:
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
Amour22015Author Commented:
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
Rey Obrero (Capricorn1)Commented:
try this query, copy and paste

INSERT INTO tblNAsCommunication ( Entry, Purpose, IsKeyPhone )
SELECT Ams_Phone.Phone, Switch([PhoneType]=34,"Work",[PhoneType]=35,"Home",[PhoneType]=36,"Mobile",[PhoneType]=43,"Fax",[PhoneType]=67,"Alternative Phone 2",[PhoneType]=68,"Alternative Phone 1",[PhoneType]=69,"Alternative Phone 3") AS Expr1, "1" AS Expr2
FROM Ams_Phone;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
I have to say using the switch statement is the best way to do this.  Great Job
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.