Transposing Data without hardcoding

Hey guys,

I’m new to access and databases and I’m having a bit of trouble coming up with a query that will do what I need. I have three tables in the form of:

Table 1:
Category ID, Category

Table 2:
OtherGroupingID, Group

Table 3
UniqueID, CategoryID, OtherGroupingID, DataPiece1, DataPiece2…


What I need to do now is transpose the data based on Category, so that I would get a table that looks like this:

OtherGroupingID, Category1Datapiece1, Category1Datapiece2… …Category2Datapiece1, Category2Datapiece2…


I know how I should start by using a crosstab query with Category as the ColumnHeading Project as the RowHeading and the UniqueID as the Value (“Table 4”). By doing this I think I have effectively transposed the data. My trouble is on the next step. How can I link Table 4 back to Table 3 to pull up the DataPieces associated with the UniqueIDs without explicitly referring to the CategoryID (I have to do this because I have no idea how many columns Table 4 ends up with – it depends completely on how many entries there are in Table 1)?

Thanks a lot – the answer to this question will be a HUGE help.

Thanks!

re: Transposing Data without hardcoding

First thing is you can't return two columns in a crosstab query.

I think the problem here is with your table structure. DataPiece should be just one column in the table. So if DataPiece has two values this would be two different records.

Then you can group on the Category and OtherGrouping and pivot on DataPiece in a Crosstab.

re: Transposing Data without hardcoding

Thanks for the response mmccarthy. Unfortunately, I don't think that adjusting my table structure would help the situation any (I just finished trying).

If I split Table 3 into Table 3A and Table 3B, such that

Table 3A
UniqueID, CategoryID, OtherGroupingID, DataPiece1
Table 3B
UniqueID, CategoryID, OtherGroupingID, DataPiece2

then we do solve the immediate problem of being able to create multiple crosstab queries. But then I'm in a pickle again when I try to relink the two cross tabs of Table 3A and Table 3B, because the crosstabs come out like this:

Table3A CrossTab
_________ Grouping1 Grouping2 Grouping3
Category1 datapiece1 datapiece1 datapiece1
Category2 datapiece1 datapiece1 datapiece1
Category3 datapiece1 datapiece1 datapiece1
Table3B CrossTab
_________ Grouping1 Grouping2 Grouping3
Category1 datapiece2 datapiece2 datapiece2
Category2 datapiece2 datapiece2 datapiece2
Category3 datapiece2 datapiece2 datapiece2



I need to get this into the same table somehow. The only way i can think of is to link accross Category. But if i do that then I either have to select * (which means repeating category b/c its in both tables), or stating specifically that I want Grouping1, Grouping2,... etc. which, of course, defeats the purpose. I guess this could be solved if there were some sort of SELECT * EXCEPT command, but failing that, I'm not sure how to approach this.

BTW, this is all complicated by the fact that we're not just talking about table 3A and 3B, its more like 3A through 3Z.

I did some reading on the subject and was wondering if i might need dynamic queries to solve this problem? Is that even possible in Access, and if it is, how might I go about doing it?

Thanks again!

re: Transposing Data without hardcoding

You need to rethink your strategy.

Table 3 should just be one table as follows:

UniqueID, CategoryID, OtherGroupingID, DataPiece

Why are you looking at a crosstab query. What do you need to represent?