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

Robert Sherman

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 Prasad

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;

Amour22015

ASKER

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.

Kanti Prasad

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

Amour22015

ASKER

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

Amour22015

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

I have to say using the switch statement is the best way to do this. Great Job