Extracting Access data for pivot table in Excel 3

[belinda7]

I have an Access (2002) table (Table 1) with the following 6 fields: Product, Manufacturer, Price, 2001_Units, 2002_Units, 2003_Units.
I want to rearrange the data to use into a Pivot table in excel.

In order to have a drop down list for the years in the Pivot table, I want to have the data into a new table layout (Table 2). There is a new field for "Year" and all the units data for the different years will be under the "Units" field. There will be 5 fields: Product, Manufacturer, Price, Year, Units.

What is the best way to do this? I’ll eventually apply this method to a table of data which is much larger and has more fields.

Table 1
Product, Manufacturer, Price, 2001_Units, 2002_Units, 2003_Units
Item 1, Smith & Co, $5.00, 420, 4523, 8626,
Item 2, Henry Associates, $2.00, 2563, 5897, 9231,
Item 3, Link Incorporated, $3.50, 1235, 3596, 5957,



Table 2
Product, Manufacturer, Price, Year, Units,
Item 1, Smith & Co, $5.00, 2001, 420,
Item 2, Henry Associates, $2.00, 2001, 2563,
Item 3, Link Incorporated, $3.50, 2001, 1235,
Item 1, Smith & Co, $5.00, 2002, 4523,
Item 2, Henry Associates, $2.00, 2002, 5897,
Item 3, Link Incorporated, $3.50, 2002, 3596,
Item 1, Smith & Co, $5.00, 2003, 8626,
Item 2, Henry Associates, $2.00, 2003, 9231,
Item 3, Link Incorporated, $3.50, 2003, 5957,

 

[mordja]

belinda7,

The most obvious and probably easiest way is to run three seperate append queries.


INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
SELECT Table1.Product, Table1.Manufacturer, Table1.Price, 2001 AS Expr1, Table1.[2001Units]
FROM Table1;

INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
SELECT Table1.Product, Table1.Manufacturer, Table1.Price, 2002 AS Expr1, Table1.[2002Units]
FROM Table1;

INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
SELECT Table1.Product, Table1.Manufacturer, Table1.Price, 2003 AS Expr1, Table1.[2003Units]
FROM Table1;



Mordja

 

[lespaul]

I think you can get it with a single query using a UNION in your select:

INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
SELECT Product, Manufacturer, Price, '2001', [2001Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, '2002', [2002Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, '2003', [2003Units]
FROM Table1

Leslie

 

[belinda7]

Thanks Morja. It worked. The only change I did was to write[2001_Units] instead [2001Units].

Thanks for your input, Lespaul. I would prefer to do this in one step and tried your approach. I got the following error message: <<Syntax error in FROM clause>>. What do I need to adjust?

 

[lespaul]

did you try just the SELECT .... UNION .... SELECT clause to see if it returns the correct information? You may want to remove the ' from around each year.



Leslie

 

[belinda7]

Thanks for your prompt response, Lespaul.
I am new at this and I don't understand what you mean.

 

[lespaul]

Does this query return what you are looking for? (notice I removed the ' from around each year)

SELECT Product, Manufacturer, Price, 2001, [2001Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2002, [2002Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2003, [2003Units]
FROM Table1

If this information seems correct, now try this:

INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
SELECT Product, Manufacturer, Price, 2001, [2001Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2002, [2002Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2003, [2003Units]
FROM Table1

Leslie

 

[belinda7]

Hi Lespaul,

The first union query worked. However, when I ran the second one, I got the error message: <Syntax error in FROM clause>>.

Belinda

 

[lespaul]

What if you surround the UNION query in ()?

INSERT INTO Table2 ( Product, Manufacturer, Price, [Year], Units )
(SELECT Product, Manufacturer, Price, 2001, [2001Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2002, [2002Units]
FROM Table1
UNION
SELECT Product, Manufacturer, Price, 2003, [2003Units]
FROM Table1)

Leslie

 

[belinda7]

Now I have the following error message: <<Syntax error in INSERT INTO statement.>>

 

[lespaul]

what kind of field is [YEAR]?

You should change that too, most words like:

DATE
TODAY
NOW

are reserved words and Access can choke on them.

Leslie

 

[PHV]

The first union query worked
So save it as, say, qryUnionTable1
And then you may try this:
INSERT INTO Table2 (Product, Manufacturer, Price, [Year], Units)
SELECT * FROM qryUnionTable1;

Leslie, I don't think that the INSERT INTO instruction admits union in the SELECT clause.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Learn something new every day!

Thanks, PHV

les

 

[belinda7]

Thank you Morja, Lespaul and PHV. I finally got this to work and also got an introduction on how to use the "Union" query. I am happy :->

Do you know of any good web site that gives a comprehensive explanation on the use of the "Union" query?

 

[belinda7]

Hello,

I combined 19 tables, each with identical fields as follows:
SELECT * FROM TABLE 1
UNION ALL
SELECT * FROM TABLE 2

.
.
.
UNION ALL
SELECT * FROM TABLE 19

I ended with my consolidated table. However I have an extra 18 rows with the field labels.

How can I prevent the field labels from being incorporated into the resulting table?