473,322 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL that use 3 columns in the same table as input to query

2
Hi,

SQL is not my main area of expertise so I need some help.

I have the following table (PhonePerson) which contains 5 columns:
PersonGUI (PK, Employee ID)
PhoneType (MOBILE,DAY,FAX,HOME)
PhoneID (1=MOBILE, 2=DAY, 3=FAX, 4=HOME)
AreaCode (Area code, ex 415)
Phone (Phone #, without area code, ex 444-4444).

I need to generate a sql that checks to see if an employee has a value for 'MOBILE' and if it does it needs to return AreadCode+' '+Phone in a column called 'Phone' for that employee. If there is no value for 'MOBILE' for a certain employee it instead needs to return the value for 'DAY' by AreaCode+' ' +Phone in the 'Phone' column.

Any help is greatly appreciated!

Thanks!
May 18 '10 #1
3 1161
QVeen72
1,445 Expert 1GB
Hi,

Not clearly understood your requirements..

Looks like you require 'AreaCode+' '+Phone " in both the cases...
simply query for that..
or else give an example with 3-4 records...

Regards
Veena
May 18 '10 #2
xstrom
2
Hi Veena,

Thanks for your quick reply. I think you understood it correctly, and yes, the answer is most likely very easy for an expert. This is an example of data in the PhonePerson table:


00001 1 MOBILE 954 444-4444
00001 2 DAY 954 555-5555
00002 2 DAY 954 666-6666


So for PersonGUI (Employee ID) 00001 I want the select statement to first check if there is a valid AreaCode and Phone for MOBILE. If there are, then it should return the value of AreaCode and Phone for MOBILE in a column called "Phone". For PersonGUI 00002 the select should again check to see if there is a valid AreaCode and Phone for MOBILE and in this case there is not, so I would like it to return the value for AreaCode and Phone for DAY instead in a custom formatted column called "Phone".


Thanks!
May 18 '10 #3
QVeen72
1,445 Expert 1GB
Hi,

You can try this :

Select PersonGUI , AreaCode & ' ' & Phone As Phone From PhonePerson Where PhoneID = 1
Union All
Select PersonGUI , AreaCode & ' ' & Phone As Phone From PhonePerson Where PhoneID = 2 And PersonGUI & '/' & CSTR(PhoneID) Not In (
Select PersonGUI & '/' & CSTR(PhoneID) From PhonePerson Where PhoneID = 1)

You can refine the query by checking for not null / blank etc...

Regards
Veena
May 19 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Siva | last post by:
Hi, i want to know, how to get the structure of the table in query format(create table ...). any one can help me. bye & thanxs Siva Prakash. M
6
by: Marcus | last post by:
Is there a way to hide columns in a query? If so, where would I find some sample code on this topic? If this can be done, is it better to hide columns in a query, or simply build a second query...
1
by: Philehof | last post by:
Hi Group. I would like to add some information to a query. I tried to create a table with the information from the query but then if i run the query again the additional data will be lost. ...
1
by: Phil | last post by:
Is it possible to swap rows and columns in select query output so that each record's data is displayed in a column? I want to collect data each day and display it in a query with each day's date...
1
by: babyjek | last post by:
SELECT ProjectID, ProjectName, ProjectDescription, SequenceNumber, LastUpdateDate FROM Project WHERE ProjectID = @ProjectID; (The Microsoft Jet database engine cannot find the input table or...
2
by: ielamrani | last post by:
Hi, this is very strange. I am trying to add 2 columns in a query but it does not come out right. Column1 Column2 Column3 $15.00 $30.00 $15.00$30.00 Column3 should equal $45.00. ...
2
rsmccli
by: rsmccli | last post by:
I am getting this error using Refreshlink from code. No problem for admins, only some normal users. It says Error 3111: you have no permissions for Table or Query TMP%#MAU@. This object does not...
6
by: warpcon | last post by:
Im trying to build a database starting with one of the templates that come with access. I took out the employee part in all the tables and forms. Now when I pull up a workorder and then try to view...
0
by: Giero | last post by:
Hello, I have a little problem with my database. I created table where one of the fields is hyperlinked and is linking to the .doc files stored on the network drive. I can get the queries and...
3
by: rinu911 | last post by:
hi all I am tring to insert a values to a table by checking if the value is there in the table already but it gives me this error "Query input must contain at least one table or query" ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.