Transposing Data

[Robert_Mc]

Does anyone know a way, or if there even is one, of transposing data (in the same way as Excel COpy-Paste Special-Transpose) to create a pivotted table?

 

[ColinEssex]

I think you need to explain in more detail what you're wanting.

As you know, pivot tables are in Excel, you can refresh the data from there which will pull it from an Access Db.

But I don't think thats what you want is it?

Col

 

[Robert_Mc]

Thanks for the reply. I am basically trying to rearrange a table. The table has been generated by Form input & has the following structure:

Customer(text)|Order No(autonumber)|Brand_1|Brand_2|etc(double)

There are 106 Brands & approximately 500 orders (rows). I need to link the Brands to a Family table structured

Brand_Family(text)|Brand(text)

to enable some analysis of quantities (avg, sum, etc) & grouping, but of course there is no "joinable" field to make this easy. It occurred to me that I could drop it all into Excel, transpose & re-title the data & then bring it back to Access, but I wanted to find out if there is a way of doing this inside Access. I suspect not, but would be more than happy to be proved wrong.

Does this help clarify? Let me know if there's any further insight I can offer.

 

[Mile-O]

Aren't crosstab queries used to sort data into Pivot Table friendly formats?

(I've never used crosstab queries, that's why I'm unsure)

 

[Robert_Mc]

Hi

I thought about using a Crosstab query, but it won't work as there is no common column heading in the table to generate a Value for summing, etc, the brands (all the brand columns in the table are unique). With 106 brands I would have to create the equivalent number of Crosstab queries, & even then I couldn't combine them as the refenence to each brand would be lost & I'd merely get a grand total.

Many thanks for the suggestion though!

Robert

 

[Mile-O]

Are you saying that you have 106 fields in a table each devoted to a different brand?

 

[ColinEssex]

Customer(text)|Order No(autonumber)|Brand_1|Brand_2|etc(double)

There are 106 Brands

_______________________________________________

Does this mean you have 106 fields called Brand_1.......to Brand_106?


Col

 

[Robert_Mc]

Hi again

That's correct, the data was originally input via a single form, with a quantity entry field for each brand; this has created the 106 fields. Now I've been asked to group them into about 10 brand groups, & this is the problem.

thanks

Rob

 

[Mile-O]

You probably are not going to like what you are going to be told next but...you need to normalise your database if you want to achieve anything with it and keep it robust.

Search on the forum for normalising or normalizing - your database is violating 2nd Normal Form

 

[ColinEssex]

What Mile-O said....

You beat me to it Mile


Robert - I can't imagine what 106+ fields must look like on a form.

Have a look at the Northwind database, it shows how to deal with categories and items within those categories. You'll need to check out "cascading ComboBoxes" as well.

Good luck mate

Col

 

[Mile-O]

Have a look at this:

It includes a normalised structure and a crosstab query to group your brand families.

 

[Robert_Mc]

Thanks to all who responded.

I've looked at the database very kindly sent by Mile-O & agree that it's much more systematic than my own effort! Unfortunately its drawback appears to be that, when inputting data, customer number & order number must be repeated manually for each brand qty entered (please bear in mind that I need a unique customer/order combination for further download into our ERP system). This would make further data input very arduous (there is more to go in!), so I'm beginning to think I'm going to have to write some grouping queries for each brand family & then build them back into a new table.

I appreciate all the help you have given, & for the record Col, 106 brands do look pretty 'busy' on a single form!

Cheers

Rob

 

[Mile-O]

It wasn't for the data entry I made the example - the fact there was data entry there was just to let you populate the table easier.

The purpose was to show a better way to store brands and group your brands into families by using related tables.

 

[Robert_Mc]

Thanks again Mile-O

I do appreciate the advice & am now considering a different approach to using Access - that is, spend more time designing & less time data dumping & then writing cumbersome queries to get at the data I really need! I'll take some time to look at normalisation to remove all the duplicated data & hopefully have slicker dbs to work with...

Not being a veteran Access user (I kind of fell into it because I can write SQL!) I tend to try & solve everything with a little 'code-play', as it were, so no doubt I'll be leaning on these forums as I learn & occasionally foul up!

Cheers

Rob

 

[Robert_Mc]

Thanks for the reply Pat. As you could probably guess, I was trying to deny myself the inevitable task of creating 106 queries! Fortunately, although there is further input to be made to the main table, this is a temporary database & I should be able to 'get away with it'...phew.

However, I'll bear database protocols in mind for the future & stop relying on unnecessarily-long-winded SQL to dig myself out of a hole.

Regards

Robert