Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access Need

Posted on 2014-09-20
10
222 Views
Last Modified: 2014-09-20
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
Comment
Question by:Rayne
  • 8
  • 2
10 Comments
 

Author Comment

by:Rayne
ID: 40334757
0
 

Author Comment

by:Rayne
ID: 40334758
please see attached DB for table
0
 

Author Comment

by:Rayne
ID: 40334776
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Rayne
ID: 40334777
its combining the three prices tiers into just one row (for bulk wholesalers)
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 40334791
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
 
LVL 16

Expert Comment

by:Sheils
ID: 40334793
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
 

Author Comment

by:Rayne
ID: 40334796
Thank you Sheils :)
0
 

Author Comment

by:Rayne
ID: 40334799
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
 

Author Closing Comment

by:Rayne
ID: 40334800
Thank you
0
 

Author Comment

by:Rayne
ID: 40334803
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question