Reporting table fields vertically

[James Martin]

I've come across a client with a non-normalized table with sales of items
for eight different regions. So, the fields in the table are:

ItemCode
Region1SalesQty
Region2SalesQty
....
....
Region8SalesQty

Typically the client is printing these sales in columns, with a report that
has the eight regions spaced horizontally across the page. Something like
this:

ItemCode Region1SalesQty Region2SalesQty ... Region8SalesQty

However, they now need a report with the sales reported vertically, like
this:

ItemCode Region1SalesQty
Region2SalesQty
...
Region8SalesQty

and so on for each item.

I realize that this wouldn't be a problem if the tables were normalized, but
changing the table layouts isn't an option now.

I'm wondering what the best way to print this report is. Right now I've made
a working table with these fields:

ItemCode
Region
SalesQty

When the report is run, it first uses append queries to populate the temp
table. I can then easily print my report off the temp table.

I'm just wondering if there's a better way to do this.

Thanks in advance.

James

 

[KARL DEWEY]

Use a union query.
SELECT ItemCode, Region1SalesQty AS QTY, "1" AS REGION
FROM YourTable
UNION ALL SELECT ItemCode, Region2SalesQty AS QTY, "2" AS REGION
FROM YourTable
..... repeat for all Region_X_SalesQty you have.
UNION ALL SELECT ItemCode, Region8SalesQty AS QTY, "8" AS REGION
FROM YourTable;