Data manipulation question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Our payroll department uses a third-party database as a backend to a frontend
MS Access 2000 database with all linked tables. Every pay period they
generate a txt file that is sent to our financial department which contains a
list of all hours worked and the shift codes (A=Regular, B=Overtime, C =
Night shift overtime, etc.). A typical recordset would look like this:

ID Dept Hours ShiftCode
1 811233 80 A
2 812200 96 A
3 812203 8 B
4 812203 8 C

The problem I'm facing (and I'll confess, I'm not the brightest bulb in the
box when it comes to SQL/VBA), is that the report going out is showing
duplicate hours. What they need the txt file to show is:

ID Dept Hours ShiftCode
1 811233 80 A
2 812200 80 A
3 812203 8 B
4 812203 8 C

I need to go through each department's group of records (identified by the
first four characters in the department ID - the last two can't be stripped
out as they represent yet another code needed by Financial), and if there is
a ShiftCode of B or C, subtract that value from A and make that value
available in the txt file and hide the value of A that is stored in the
backend db. I'm not allowed to overwrite the values in the backend database.

I then need to perform this same action again for other sets of ShiftCodes
(fa/f1b, f2b, K/L, and so on).

I am allowed to do this anywhere in Access, as long as the results are
exportable as a txt file.

Any help (even referring me to a more appropriate newsgroup) would be very
welcome.

Thanks in advance,
Trisha
 
Hi,



SELECT Dept \ 100, SUM( iif( ShiftCode = 'A', hours, -hours)
FROM tableName
GROUP BY Dept \ 100



If you have more ShiftCode, that could be easier to describe the logic in a
table just for that:


Logic 'table name
ShiftCode, Operation ' fields name
'A', 1
'B' -1
'C' -1
....



and then



SELECT Dept \ 100, SUM( operation * hour)
FROM tableName INNER JOIN logic
ON tableName.ShiftCode=logic.ShiftCode
GROUP BY Dept\100




Hoping it may help,
Vanderghast, Access MVP
 
Thank you for your suggestions.

This was helpful in getting the data formatted to allow for summing of the
different shift codes, but am still confronted with producing a recordset
with the the calculations already completed.

I'm having a hard time conceptualizing how to make this work without
resorting to a crosstab query using the ShiftCodes as column headers then
manually calculating A-B or A-C or K-L or E-F*, etc.

I appreciate your help getting this far, my journey with this problem just
isn't over yet.

Thank you,
Trisha
 
Back
Top