"transposing vertical data into horizontal columns"

[stevelondon]

Hi - Currently I'm manipulating product sales data that exists in a regular database table into one where each product sale is shown in a separate column for each product group. This query is then linked to excel so that other people can carry out further calculations on the data in a way that they're used to and with data from outside the database.

Product sales table fields are:
company (A,B,C) etc
Product code (1,2,3) etc


sales numbers

e.g.
Company A, Product 1, 10 sales,
Company A, Product 2, 20 sales
Company B, Product 1, 15 sales

That needs to be in form:
Company A, 10 product 1 sales, 20 product 2 sales,
Company B, 15 product 1 sales, 30 product 2 sales
etc.

I could easily pivot table this out, but in order to get excel's index(match) to work easily on this I'm using a query per product group that is being joined to a master list of companies. I've experimented with aliasing the sales table multiple times, so that I can do all this work in one query, using different "where" criteria for each aliased table. However, I can't get this to work, as it's either only showing data where product sales are non null across all selected product groups, or showing multiple rows of data depending on the join type.

Apart from the logic of the exercise I'm doing (?!) is it possible to alias tables to get them to work in the way I want?
thanks

[June7]

Not following all this. What do you mean by 'excel's index(match)'? By 'aliasing the sales table multiple times' you mean multiple copies of the sales table in the query? Show the query sql you attempted.

How many product groups?

Review these
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

[stevelondon]

Hi June,
Hope this isn’t too detailed / boring etc – I’m self taught so it’s probably full of idiosyncrasies!
There are approx 15 product groups, and I’ve done this the long way round now by making 15 queries and joining them to the master list of banks at the end. I’d still be interested to know if it could be done a more elegant way?
What I meant by excel’s index(match) is that in the end user spreadsheet there is a list of approx 120 (unique) banks with data in columns. The end users use an index (match) type formula to look up company ID in order to perform further calculations to data in later columns.
If I pivot table the access sales table with column axes of product group, row axes of company ID and data of sales this gives me the desired result. However, index(match) gets a bit temperamental when matching within pivot tables in excel so my best suggestion is to produce this flat excel datasheet created by the queries I’ve described. Sorry if this isn’t clear, hard to explain over text!
So an individual product query looks like this (TBL PARAMETERS is to restrict data values to THIS YEAR = 2011 in this case):

QRY_SALES_PERSONAL_LOAN
SELECT SALES_YEAR.BANK_ID, SALES_YEAR.NUM_RETAIL_CUSTOMERS AS NUM_RETAIL_CUSTOMERS_PERSONAL_LOAN,
FROM TBL_PARAMETERS, PROD_SALES_YEAR
WHERE (((PROD_SALES_YEAR.PRODUCT_GROUP)="PERSONAL_LOAN") AND ((Val([PERIOD]))=[TBL_PARAMETERS].[DASHBOARD_TY]));

Incidentally I haven’t made a table join as [PERIOD] in the database where all this is coming from is not a number (Not my choice!)so needs VAL to convert to number. If I do VAL around the join I can no longer edit in the editor which I find easier with multiple queries.

The separate product queries are joined in the following query to the master table:

FINAL JOINING QUERY
SELECT BANKS.BANK_ID, BANKS.BANK_NAME, QRY_SALES_PENSIONS.NUM_RETAIL_CUSTOMERS_PENSIONS, QRY_SALES_PERSONAL_LOAN.NUM_RETAIL_CUSTOMERS_PERSO NAL_LOAN
FROM (BANKS LEFT JOIN QRY_SALES_PENSIONS ON BANKS.BANK_ID = QRY_SALES_PENSIONS.BANK_ID) LEFT JOIN QRY_SALES_PERSONAL_LOAN ON BANKS.BANK_ID = QRY_SALES_PERSONAL_LOAN.BANK_ID;

What I was hoping to get was a query like (edited query to remove [period]for ease of code)…

SELECT SALES_YEAR.BANK_ID, SALES_YEAR.NUM_RETAIL_CUSTOMERS AS NUM_RETAIL_CUSTOMERS_PERSONAL_LOAN, SALES_YEAR.NUM_RETAIL_CUSTOMERS AS NUM_RETAIL_CUSTOMERS_PENSIONS
FROM BANKS LEFT JOIN SALES_YEAR ON BANKS.BANK_ID = SALES_YEAR.BANK_ID) LEFT JOIN SALES_YEAR AS SALES_YEAR_1 ON BANKS.BANK_ID = SALES_YEAR_1.BANK_ID
WHERE (((SALES_YEAR.PRODUCT_GROUP)="PERSONAL_LOAN")) OR (((SALES_YEAR_1.PRODUCT_GROUP)="PENSIONS"));

I’ve played around with the “where” clauses but I am getting multiple rows of data per bank whereas I want a row per bank with data if there is data and no data if there is not.

I’ve used this multiple join type many before when I don't have to use a where clause for each table (as the data in the query being joined is already filtered) and it works ok - it's just doing it all together seems to cause issues. Many thanks for looking! J

Steve

[June7]

Your original scheme seems best, other than a VBA solution.