I have a table that is imported from a fixed-width flat file that is exported from an IBM AS400 system. (Attached) The table contains work orders (WO). Some orders are a single record, and some are 3 records, with the certain fields changed for different information. For example, one might have a single instruction field, and the others may have 3 instruction fields, but with the same WO number.
EXAMPLE 1: ID 2 is a WO with a single record
EXAMPLE 2: IDs 3, 4 , and 5 have the same information and WO number, except for different in fields PZLineType and PZDate
I am trying to generate a WO/report for each WO number that fills in the PZLineType and PZDate for that order, regardless of whether it is a 1 line or 3 lines.
I am confused on how to differentiate the repeating fields. That is how to have the first PZLineType and PZDate in a pair of text boxes, then the second PZLineType and PZDate in another pair of text boxes, etc.
Should I query and combine to a temp table, then run the report from the temp? I am not sure what the syntax would be for this. I know it needs a JOIN, but not sure about how to combine the 3 records into 1 with numerated fields (PZLineType1 and PZDate1, PZLineType2 and PZDate2, etc.).
Any help would be greatly appreciated.