Solved

Removing field data within a Query

Posted on 2012-03-27
6
249 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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