Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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
0
Rayne
Asked:
Rayne
  • 8
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now