Hi All,
I've a pretty challenging work to do, I am trying to use di ACCESS SQL to transpose rows/columns, till now, with no luck.

I've tried the TRANSFORM instruction but the reuired columns to output are not fixed, so this approach doesn't get the point...


Please, see the attached file for an example.
Click to view attachment


Any suggestion wll be very very well accepted

Your described output would NOT be very useful, I'm afraid. Therefore, you ought to rethink it anyway. You show THREE instances of Description, THREE instances of Value1, and THREE instances of Value2. That means the user will have to scratch his head trying to figure out WHICH instance of Value1 refers to which of the Descriptions. You could tell them to use the relative position, left to right, of the columns, but that is asking a lot of someone who may see the output without getting the explanation.

A better way to do this would be to incorporate the descriptions and ProductCodes into a SINGLE column to be pivotted into the output. You can concatenate them as needed:

ProductCode & " " Description as ProductDescription

That becomes the row header.

I'll try to come up with some SQL if you can provide a bit of actual sample data to work with. My air-SQL is not so good <grin>

Thanks for the very quick reply.

I've attached an excel file with the structire of the INPUT/OUPUT data (Ican't post real data...):


Click to view attachment

Given that this Excel file merely reproduces your screen shots, I can produce something sort of somewhat similar with this data, but you'll have to modify it to work with your Access tables.

Let me see what I can do with this bit of data.

Take a look at the queries in the attached sample.

They reflect the data in the sample you provided, but it is quite likely your real data will differ enough to require further adjustments.

I repeat, the design you presented in your screen shot is not optimal for presenting the data in a logical, easily understood format, in any case.

While this presentation does differ quite a bit , and may be further refined as you apply it to your data, it has the advantage of grouping and sorting the data into meaningful columns and rows.

Click to view attachment