Removing field data within a Query

I have created the query below to give me the sum of use within each job code.  Each Job Code begins with 4 numbers and some are followed by letters.  What i need to do is eliminate the letters so those combine reducing my final results.  Below is my SQL and a sample of the job codes.  

SELECT employeeinfo.JOBCODE, Sum(dragon.TotalDuration)/360 AS SumOfTotalDuration
FROM dragon INNER JOIN employeeinfo ON dragon.LastLoginName = employeeinfo.LOGNAME
GROUP BY employeeinfo.JOBCODE
HAVING (((Sum(dragon.TotalDuration)) Is Not Null));


Job Code     Ave Utilization
5011      1586.852778
5011I      1412.311111
5011M      713.1916667
5016B      1040.788889
5016C      2714.802778
5017AS      334.9055556
5017C      9301.636111
5017X      19708.25
5024      85877.25
5024C      173330.4889
5024K      12.80833333
5024NC      11268.81944
5025      114675.5222
5025C      90259.66111
5025K      2095.180556
jsawickiAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
SELECT val([employeeinfo].[JOBCODE]) as [Job Code], Sum(dragon.TotalDuration)/360 AS SumOfTotalDuration
FROM dragon INNER JOIN employeeinfo ON dragon.LastLoginName = employeeinfo.LOGNAME
GROUP BY val([employeeinfo].[JOBCODE])
HAVING (((Sum(dragon.TotalDuration)) Is Not Null));
0
 
Rey Obrero (Capricorn1)Commented:
use val([Job Code]) to remove the tailing text from the field

select val([Job Code]) as JobCode,
0
 
jsawickiAuthor Commented:
Thanks, What does the val do so i can use it in the future for these situations.  Am i right on saying that it recognizes the minimum number of characters for all lines and removes any additional?
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.

 
DoveTailsCommented:
If your job codes will always be the first four characters, another option you can use is the Left function...
Left([Job Code], 4)

Depends on the format you expect your data to be in.
0
 
Rey Obrero (Capricorn1)Commented:
val("1234ABC")  will give you 1234


val("1ABC")  will give you  1


val("1234A")  will give you  1234


is that clear enough

here is the definition of val() function

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes
0
 
jsawickiAuthor Commented:
thanks all, i realized that once i looked at my codes and saw some only have 3 numbers.  As always i appreciate the help Capricorn.
0
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.

All Courses

From novice to tech pro — start learning today.