Convert summary list to detail list SQL MS Access
Posted on 2011-03-08
I am looking for an efficient way to turn:
SKU priceshed R1 R5 W1 W5 W10 W25 W50
1099 branch $5 $3 2.99
1089 branch $1 .99 .75 .69
where R1 is the retail price for 1 item, W1 is the wholsale price for 1item
And turn it into
SKU priceshed Rate From To Price
1099 Branch Retail 1 9999 $5
1099 Branch Wholesale 1 9 $3
1099 Branch Wholesale 10 9999 $2.99
1089 Branch Retail 1 4 $1
1089 Branch Retail 5 9999 $3
1089 Branch Wholesale 1 24 $.75
1089 Branch Wholesale 25 9999 $.69
There will always be an R1 and W1 with any combination of the rest.
9999 is the max number on the "to" count.
Right now I am using multiple queries to accomplish this. I would like to create a VB script to do the work. Once this conversion is complete the converted table is in the same format as the production system pricing guide and then we compare the two and make changes. The origin format is to make it easy on the person who has to update pricing on many items relatively often.
Any ideas would be greatly appreciated.