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: Ams_Phone.phone = 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
Then:
tblNAsCommunication.Purpose = Work And tblNAsCommunication.IsKeyPhone = "1"

I need to setup a query syntax to do this?

Thanks for any help.
Amour22015Asked:
Who is Participating?
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..
0
Kanti PrasadCommented:
Hi

you can look into the below link and frame your sql
http://www.techonthenet.com/sql/insert.php

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

INSERT INTO tblNAsCommunication tnc
(tnc.Purpose)
SELECT ap.phonetypes FROM Ams_Phone ap where ap.phonetypes = 34;
0
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:
tblNAsCommunication

So yes table tblNAsCommunication already exists

The phone numbers from:
Ams_Phone.phone 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 = Ams_Phone.phone
tblNAsCommunication .Purpose = "Work"
tblNAsCommunication .IsKeyPhone = "1"

Thanks for helping me.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kanti PrasadCommented:
sorry it should phonetypesvalue  replace it with the column name where you have the 'work' stored

INSERT INTO tblNAsCommunication tnc
(tnc.Purpose)
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 .......
0
Amour22015Author Commented:
This is mentioned:
SELECT ap.phonetypesvalue  FROM Ams_Phone ap where ap.phonetypes = 34;

But ap.phonetype changes.

So Ap.PhoneType =

34
35
36
43
67
68
69

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

Please help and thanks
0
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
 Then
 [tnc].[Purpose] = "Work"
 [tnc].[IsKeyPhone] = 1)
 Else

Please help and thanks
0
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;
0

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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.