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?

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

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

Rey Obrero (Capricorn1)Commented:
use val([Job Code]) to remove the tailing text from the field

select val([Job Code]) as JobCode,
0
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

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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