Access Need

Hello,
There is a table in access that has items and their mode markets where they are sold. Within each markets, there are grouping of prices of that very item.
 Now I want to take that table and transform it so that price grouping is separate rows. Please see attached. How do I accomplish this in access VBA?
Thank you
access-transform.xlsx
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayneAuthor Commented:
0
RayneAuthor Commented:
please see attached DB for table
0
RayneAuthor Commented:
I did this query but its combining the three prices tiers into just one row ( there should be three sepearate rows as shown in the excel)

SELECT mainTable.item, mainTable.modeEntry, mainTable.[Tier1 Cost] as [Tiers] FROM mainTable
union
SELECT mainTable.item, mainTable.modeEntry, mainTable.[Tier2 Cost] as [Tiers]  FROM mainTable
union
SELECT mainTable.item, mainTable.modeEntry, mainTable.[Tier3 Cost] as [Tiers] FROM mainTable
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RayneAuthor Commented:
its combining the three prices tiers into just one row (for bulk wholesalers)
0
SheilsCommented:
You don't need vba for this it can be achieved by a Union Query. The sql is as follows

SELECT item, modeEntry,"Tier 1 Cost" as fldTierGrouping, [Tier1 Cost] FROM mainTable

UNION


SELECT item, modeEntry,"Tier 2 Cost" as fldTierGrouping, [Tier1 Cost] FROM mainTable

Union

SELECT item, modeEntry,"Tier 3 Cost" as fldTierGrouping, [Tier1 Cost] FROM mainTable

Open in new window


Just copy the code above. Click create query in your sample. Don't add any table to the query. Go to sql view and paste the code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SheilsCommented:
Sorry got a cut & paste error. SQL should be

SELECT item, modeEntry,"Tier 1 Cost" as fldTierGrouping, [Tier1 Cost] FROM mainTable

UNION


SELECT item, modeEntry,"Tier 2 Cost" as fldTierGrouping, [Tier2 Cost] FROM mainTable

Union

SELECT item, modeEntry,"Tier 3 Cost" as fldTierGrouping, [Tier3 Cost] FROM mainTable

Open in new window

0
RayneAuthor Commented:
Thank you Sheils :)
0
RayneAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Rayne's comment #a40334796

for the following reason:

good, great
0
RayneAuthor Commented:
Thank you
0
RayneAuthor Commented:
not sure what happened previously when i tried to reward points, I tried a second but it worked. Thank you for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.