Table conversion problem 1

[pachad]

Hello all.

I currently have the following table:
[tt]
|---------| Cars | SUV | Trucks |
| Plant A | 4 | 5 | 6 |
| Plant B | 3 | 4 | 2 |
[/tt]
I would like to convert this to the following, with PlantID and ProductID as the primary keys. A separate table would contain PlantID and associated info, and ProductID and associated info.
[tt]
|PlantID|ProductID|ItemCount|
| A | Car | 4 |
| A | SUV | 5 |
| A | Truck | 6 |
| B | Car | 3 |
| B | SUV | 4 |
| B | Truck | 2 |
[/tt]
How can I do this in Access? I tried using an append query, but I can't figure out how to separate each column onto its own row. The append query only lets you create a single row at a time. How can I have it add each catagory as its own row?

Thanks.

 

[PHV]

Use an union query:
SELECT Plant, 'Car' AS Product, Cars FROM yourTable
UNION SELECT Plant, 'SUV', SUV FROM yourTable
UNION SELECT Plant, 'Truck', Trucks FROM yourTable

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

 

[pachad]

Perfect!

Thanks for the quick response.

Note to users:

In access, to create a UNION query see this link:

http://www.ehow.com/how_13580_create-sql-query.html

Then create a separate append or create table query to insert the data into the required table.

 

[pachad]

Followup question:

How do I re-assemble the original data for a report from the new table?

The report looks like the orginal table:

[tt]
|---------| Cars | SUV | Trucks |
| Plant A | 4 | 5 | 6 |
| Plant B | 3 | 4 | 2 |
[/tt]

Thanks

 

[PHV]

Have you tried to follow the CrossTab query wizard ?

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

 

[pachad]

CrossTab only allows me to do aggregate functions.

I added a new field to the new table called year, and want to show actual counts broked down by year.

Report would look the same, but would have Year set to 20XX in the Record Source query.

I tried using grouping, but that gives me this:

[tt]
|---------| Cars |
| Plant A | 4 |
| Plant B | 3 |

|---------| Trucks |
| Plant A | 5 |
| Plant B | 4 |

|---------| SUVs |
| Plant A | 6 |
| Plant B | 2 |
[/tt]

Since each item is stored on its own row, it is on its own row in the report.

How can i combine multiple row's data into a single row with multiple columns?

 

[PHV]

CrossTab only allows me to do aggregate functions
As you only have one value for each PlantID,ProductID pair you may use the First (or Min, Max, Last, ...) aggregate function on ItemCount

Another way:
SELECT PlantID, Sum(IIf(ProductID='Car',ItemCount,0)) AS Cars, Sum(IIf(ProductID='Truck',ItemCount,0)) AS Trucks, Sum(IIf(ProductID='SUV',ItemCount,0)) AS SUVs
FROM yourTable
GROUP BY PlantID

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

 

[pachad]

As you only have one value for each PlantID,ProductID pair you may use the First (or Min, Max, Last, ...) aggregate function on ItemCount

Once I added in the Year field, I now have more than one value for a PlantID, ProductID pair, and using the Crosstab gives the SUM for ALL of those rows.