Data manipulation question

[Trisha B.]

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

 

[Michel Walsh]

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

 

[Trisha B.]

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