?
Solved

Access Need

Posted on 2014-09-20
10
Medium Priority
?
225 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
[X]
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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 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