Solved

Access Need

Posted on 2014-09-20
10
221 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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