Solved

Removing field data within a Query

Posted on 2012-03-27
6
247 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 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