complicated cross tab?

[snipe]

Hey guys



Here is an example of a customer's order. The ItmCode field are the protection plans that are associated with that ticket number. There can only be a max of four protection plans per order. I didn't design the current design, I just did some snooping and found out that we have been over paying the protection plan company because of this flawed design. I need to transpose this data somehow so that each order is only 1 record, and the type of protection plans fill in the ItmCode1-4 fields. If there were only 2 plans for an order, ItmCode 3 and 4 would be null.

Current:


Desired:

[June7]

If the original data had a sequence number for the records of each OrderType group, then the CROSSTAB would be simple. OrderType would be the row header, Sequence would be the column header, and ItmCode would be the value (First() aggregate function).

How many different protection plans are possible? Here is method for emulating the CROSSTAB: http://www.datapigtechnologies.com/f.../crosstab.html

Another approach is VBA code writing records to a temp table (temp because the data is purged after each run of the procedure).

[snipe]

There are 4 different plans that are available. I was under the impressed that the Value always had to be a number type. Noobie error.

You slightly lost me with the sequence number bit.

Our database does use DocLineNum. Here is a snip. The 2 highlighted rows are the protection plans. The farthest column to the left is the line number. The protection plans are not always on the same line, though. I don't have that field readily available in access currently, but I can get it.

[snipe]

Oh wow. Just spent 6 hours on this problem only to realize it won't fix the issue with us over paying. No point in fixing this. Sorry June7, thanks for the help anyways.