Solved

Removing field data within a Query

Posted on 2012-03-27
6
251 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:jsawicki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

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

select val([Job Code]) as JobCode,
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 37774088
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
 

Author Comment

by:jsawicki
ID: 37774125
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 5

Expert Comment

by:DoveTails
ID: 37774134
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37774142
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
 

Author Comment

by:jsawicki
ID: 37774156
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question