Form Query return from Numeric to Alpha Numeric 2

[Tuck007]

I'm not sure if this is a query or report type of question. Our present system wants a specific file format for the payroll. Employee #, JOb #, Hour Type (E01 - E05), and Amount of hours. Three of these are easy and done. The Hour Types is what I have questions about. The database has them stored as numeric values (1, 44, 29, etc...) that doesn't fit with the format. When Regular hours are returned fro mthe query (1) I would like either the query or a my report that is handling the final output to show E01 (44 from the database would show E02 for Travel time etc..). Suggestions? Ids this something I can write up in the report? or is this something that can be handled by the query?

.:TUCK:.

 

[traingamer]

It can easily be done in a query if you have another table of 'HourTypes'.
Id HourType
44 E01
1 E99
etc.
Just join the table and the query does all of the work.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

You may consider a lookup table for conversion:
intHourType = 44 giving strHourType = 'E02'
You'll join this table in your export query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tuck007]

I think I follow you, create a seperate table that uses the ID number that wopuld match the hour types from the previous table, join them with either a left or right, and it will show the proper code. Thanks! I was playing around with IF statements in the query, and your suggestion work out much cleaner.

.:TUCK:.

 

[rac2]

There are a couple of ways to handle that.

One is to make a table of code with a column for the numerical values and another column for the alternate code that you may wish to use in some reports. Use a JOIN to put that recode table together with the basic table when you need to show the E01 values. Like so.

SELECT JOb #, hour_type
FROM Payroll
JOIN RecodeHourType ON  RecodeHourType.code = Payroll.code

In the above RecodeHourType is the new table with two columns. One column is named code and it has the numerical values such as 44. The other column is named hour_type and it has the corresponding codes for the Hour Type such as E01.

This is a neat solution as it can be extended when new numerical codes are added. And it can be used to map multiple numerical codes into the same letter codes, e.g. maybe codes 44 and 144 are both E01 types.


The other solution is to use IIf(condition, value for true, value for false) functions. Like so.

SELECT JOb #,
            (  IIF( code = 44, "E01", IIf(code = 29, "E02", "E03) )  )
            AS "hour_type"
FROM Payroll

Here the expression is given the name "hour_type". It means if code is equal to 44 then show E01; else if code = 29 then show E02; otherwise show E03. For 5 categories there would be more IIf functions, nested to give the desired result.

This might seem easier and perhaps more intuitive, but in the long run it will be more trouble.

 

[Tuck007]

So far, all the suggestions have been right on the mark, thanks! I have one other item I am trying to work out for this query.

I think I want to use a IF(contition,THEN|ELSE) type of statement to display regular hours versus OT hours. The PayChex system that this gets fed into reads each line item, depending on the Hour Type code, that I've already format (Thanks again!), it reads the hour shown as that type of hour. Here's an example of the output:

Emp #     Job #    Type      Hours
1269      8732      E01       00080.00
1269      8732      E03       00012.00

Here you can see the same employee has performed 80 hours of Regular time (E01) and 12 hours of OT time (E03). tblTK_HOURS stores both HOURS and OVERTIMEHOURS. My thinking was something like this:

IF(HOURTYPE="E03",OVERTIMEHOURS|HOURS)

This would, in my thinking, show OT hours when the condition is true, and show regular hours when it's false (Travel Time {E02} gets stored as regular hours and is displayed when false as well, but has a different ID, just a lucky coincidence). Is there a better why to do this and do I have the syntax right here?

.:TUCK:.

 

[PHV]

The correct syntax:
IIf(HOURTYPE="E03",OVERTIMEHOURS,HOURS)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tuck007]

Access converts it all to STRING values. The SQL looks like this in the end and it doesn't want to work out right:

SELECT DISTINCTROW qryTK_HOURS.EmployeeNumber AS [Emp Num], tblJB_JOBS.UserCodeT_1 AS [Job Num], tblByChris_Hour_Types.PayChexID AS [Hour Type], Sum(qryTK_HOURS.OvertimeHours) AS [OT Hours]
FROM (qryTK_HOURS LEFT JOIN tblByChris_Hour_Types ON qryTK_HOURS.CategoriesDetailID = tblByChris_Hour_Types.CatagoriesID) LEFT JOIN tblJB_JOBS ON qryTK_HOURS.JobNumber = tblJB_JOBS.JobNumber
WHERE (((qryTK_HOURS.WorkDate) Between DateAdd("d",-13,[Enter Pay Period Ending Date]) And [Enter Pay Period Ending Date]))
GROUP BY qryTK_HOURS.EmployeeNumber, tblJB_JOBS.UserCodeT_1, tblByChris_Hour_Types.PayChexID
HAVING (((Sum(qryTK_HOURS.OvertimeHours))=IIf("PayChexID"="E03","OvertimeHours|Hours")));

.:TUCK:.

 

[PHV]

Replace this:
Sum(qryTK_HOURS.OvertimeHours) AS [OT Hours]
By this:
Sum(IIf(tblByChris_Hour_Types.PayChexID="E03",qryTK_HOURS.OvertimeHours,qryTK_HOURS.Hours)) AS [Hours]

And get rid of the HAVING clause.
You may also remove the DISTINCTROW predicate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tuck007]

YOu know when you've been staring at something and you know the answer is there... then someone comes along and says, try this, and you do, and the light goes on and everything is clear and you go "Ooooooh god I'm dumb sometimes." Thanks!

.:TUCK:.