column indirection, dynamic column names, ...

  • Thread starter Thread starter David McCulloch
  • Start date Start date
D

David McCulloch

I receive a spreadsheet that is imported into Access. Abstracted, its
format is (with column headers):

Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
Part1, $1000, $2000, $1500, ...
Part2, $5000, $5500, $6500, ...
Part3, $3000, $2500, $4000, ...
etc...

There are about 1,000 Parts and at least 12 Vendors that are subject to
change.

I want a query that creates a normalized version of the above data:

Part, Vendor, Price
Part1, "Vendor1", $1000
Part1, "Vendor2", $2000
Part1, "Vendor3", $1500
Part2, "Vendor1", $5000
Part2, "Vendor2", $5500
Part2, "Vendor3", $6500
Part3, "Vendor1", $3000
Part3, "Vendor2", $2500
Part3, "Vendor3", $4000

I could create a table of Vendors and generate a query that would give me
all combinations of Parts and Vendors. Unfortunately, how would I set
Price? If Access queries supported something like "column indirection" or
dynamic column names, I could define the Price update field to be something
like "[tbl1.[tbl2.Vendor]&"_Price"]". In row1, that would substitute to
"[tbl1.Vendor1_Price]" and then to $1000.

Any suggestions?

Dave
 
Ouch. I was hoping for something more flexible to accommodate frequent
changes. Perhaps I could use VBA to loop through all rows in my Vendor
table and dynamically build update queries. Not clean, but much more
flexible. I've never done this, so I have some more reading to do...

Dave


KARL DEWEY said:
I think you will need 12 queries, one for each vendor. Use a criteria of
Is
Not Null.

David McCulloch said:
I receive a spreadsheet that is imported into Access. Abstracted, its
format is (with column headers):

Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
Part1, $1000, $2000, $1500, ...
Part2, $5000, $5500, $6500, ...
Part3, $3000, $2500, $4000, ...
etc...

There are about 1,000 Parts and at least 12 Vendors that are subject to
change.

I want a query that creates a normalized version of the above data:

Part, Vendor, Price
Part1, "Vendor1", $1000
Part1, "Vendor2", $2000
Part1, "Vendor3", $1500
Part2, "Vendor1", $5000
Part2, "Vendor2", $5500
Part2, "Vendor3", $6500
Part3, "Vendor1", $3000
Part3, "Vendor2", $2500
Part3, "Vendor3", $4000

I could create a table of Vendors and generate a query that would give me
all combinations of Parts and Vendors. Unfortunately, how would I set
Price? If Access queries supported something like "column indirection"
or
dynamic column names, I could define the Price update field to be
something
like "[tbl1.[tbl2.Vendor]&"_Price"]". In row1, that would substitute to
"[tbl1.Vendor1_Price]" and then to $1000.

Any suggestions?

Dave
Click to expand...
Click to expand...
 
Sounds as if you are looking for a UNION query.

SELECT Part, "Vendor1", Vendor1_Price as Price
FROM YourTable
UNION ALL
SELECT Part, "Vendor2", Vendor2_Price
FROM YourTable
....
UNION ALL
SELECT Part, "Vendor12", Vendor12_Price as Price
FROM YourTable

This will normalize the data. UNION queries are NOT updateable, but you can
save the above query and use it as if it were a table to do searches, sorts,
other queries, and reports.
 
Thanks, John. This is a more elegant solution than creating multiple
queries. It won't automatically accommodate a change in vendor, but I could
build and run a single UNION query in VBA the same way that I was thinking I
would have to build and run multiple queries when I replied to Karl. Thanks
again!

Dave

John Spencer said:
Sounds as if you are looking for a UNION query.

SELECT Part, "Vendor1", Vendor1_Price as Price
FROM YourTable
UNION ALL
SELECT Part, "Vendor2", Vendor2_Price
FROM YourTable
...
UNION ALL
SELECT Part, "Vendor12", Vendor12_Price as Price
FROM YourTable

This will normalize the data. UNION queries are NOT updateable, but you
can save the above query and use it as if it were a table to do searches,
sorts, other queries, and reports.


David McCulloch said:
I receive a spreadsheet that is imported into Access. Abstracted, its
format is (with column headers):

Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
Part1, $1000, $2000, $1500, ...
Part2, $5000, $5500, $6500, ...
Part3, $3000, $2500, $4000, ...
etc...

There are about 1,000 Parts and at least 12 Vendors that are subject to
change.

I want a query that creates a normalized version of the above data:

Part, Vendor, Price
Part1, "Vendor1", $1000
Part1, "Vendor2", $2000
Part1, "Vendor3", $1500
Part2, "Vendor1", $5000
Part2, "Vendor2", $5500
Part2, "Vendor3", $6500
Part3, "Vendor1", $3000
Part3, "Vendor2", $2500
Part3, "Vendor3", $4000

I could create a table of Vendors and generate a query that would give me
all combinations of Parts and Vendors. Unfortunately, how would I set
Price? If Access queries supported something like "column indirection"
or dynamic column names, I could define the Price update field to be
something like "[tbl1.[tbl2.Vendor]&"_Price"]". In row1, that would
substitute to "[tbl1.Vendor1_Price]" and then to $1000.

Any suggestions?

Dave
Click to expand...
Click to expand...
 
Back
Top