Thank you John and Zahid:

The dilema is: if I *eliminate* transaction amounts from transaction file and rely on the detail file to hold them, the code necesary to collect information in the way I need it becomes far more complicated and runs more slowly too. Explanation later on.

This is the file structure:

1) Batch file: Batch Number (PK), batch information

2) Transaction file: Batch Number (FK), Transaction Number (PK), Employee Number (FK), Employee information that may change through time such as salary, department, etc. I have also been keeping important transaction amounts here, such as Regular hours worked, overtime hours worked, regular pay, overtime pay, comissions, vacation pay, total pay, different taxes witheld, net pay, etc. In a fully 3rd normal form structure these amounts would only exist in the detail file.

3) Proposed detail File: Transaction number (FK), perhaps a detail ID (PK) to follow theory fully, classification data (cost center (FK), project (FK)), income type (FK to description file), hours (if relevant), amount.

4) There is a separate description file that has one record per income type and indicates which income types are regular pay and which overtime, which are taxable, which accrue for vacation, etc.

In order to prepare a report or perform a procedure (such as calculating vacation pay) I need a cursor that has one record for each relevant transaction, and that record must contain a field for each income type in that transaction.

The question is, is there a simple SQL way to create such a cursor from the proposed file structure.

John, the n01, n02 fields show up if I create an empty table with one column for each income type (n01 for income type 1, n02 for income type 2, etc) and then fill then by scanning through the transaction and detail files. The only way I know how to create this cursor is a slow, clumsy process which involves creating an empty record for each transaction and filling it by scanning through the detail file for that transaction.

This is a problem that must have been solved thousands of times, but I don't know of a practical solution, other than keeping totals for the important categories in the transaction file.

I welcome all help.

Alex


>Hey Alex!

>
>Post the relevent file structures. I think you lost me on the "n01" stuff but I believe it's possible once you explain this a bit better.
>
>>In a batch/transaction/detail file group there is a conflict between a structure that offers flexibility, and one that makes reporting easier and faster. How do you resolve that conflict?
>>
>>In my case (a payroll program), different detail types follow different rules. For example, some are tax exempt, some accrue towards vacation, some are considered overtime, etc.
>>
>>So far, I have not saved much historical detail but included "subtotal" fields in the transaction history file for each of the categories and report from those. Now I am being asked to break history down further and I am wondering if now is the time to follow "theory" and calculate all totals at report time.
>>
>>
>>This brings me to the question:
>>
>>Assume that a detail file has Transaction Number, cost center, project code, income type, hours, and amount.
>>
>>Is there quick and easy way (SQL Select statement) that will total all income types for each transaction. I think you have to create the records one by one with columns such as n01, n02, etc. and fill them the slow way (Scanning through detail file).
>>
>>Perhaps I should consider a hybrid? I know it would not be third normal form then. How do you people resolve this problem.
>>
>>TIA,
>>
>>Alex