transpose results

[ironwood9 via AccessMonster.com]

My table looks like:

tblSales

CustNum.......Code.............Amt
15893............A4..............15.10
15893............BK..............17.10
15893............Z4..............19.10
16893............TR..............12.10
16893............UK..............17.15
16893............WR.............22.50
16893............YT................5.53

can I get the data from my query to look like:
CustNum....Code1....Amt......Code2....Amt .....Code3....Amt....Code4....Amt...
.Code5....Amt//Code15....Amt
15893..........A4.....15.10.....BK........17.10......Z4......19.10............
......................................
16893..........TR.....12.10.....UK........17.15......WR.....22.50......YT....
5.53...............................

(No cust would have more than 15 sales codes with amounts if that makes a
difference)

 

[Sylvain Lafontaine]

This query is not easy because the same code doesn't repeat for each CustNum
in the same column. The most easy way would be to use a temporary table to
build your result. If you don't want to use a temporary table, then you
should add an order number and use it to build your result using IIF()
statements using a serie of Left Join:

CustNum.......Code.............Amt OrderNo
15893............A4..............15.10 1
15893............BK..............17.10 2
15893............Z4..............19.10 3
16893............TR..............12.10 1
16893............UK..............17.15 2
16893............WR.............22.50 3
16893............YT................5.53 4

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 T1 Left JOIN Table1 AS T2 ON (T1.CustNum = T2.CustNum and
T2.OrderNo = 2)) Left Join Table1 as T3 ON (T1.CustNum = T3.CustNum and
T3.OrderNo = 3)) Left Join Table1 as T4 on (T1.CustNum = T4.CustNum and
T4.OrderNo = 4)
Where T1.OrderNo = 1
WITH OWNERACCESS OPTION;

If IdTable is the primary key of the table, the following query will
automatically create the OrderNo field:

Select CustNum, Code, Amt, (Select Count(*) from Table1 as T2 where
T2.IdTable <= T1.IdTable and T2.CustNum = T1.CustNum) as OrderNo
From Table1 as T1

On SQL-Server, this work perfectly. However, on Access, this doesn't work;
as JET is known to bug when LEFT JOIN are too complex but if you use a table
with the column OrderNo already prepared, it works. Bad new is that when
created, you cannot open this query in the query designer any more without
Access rewriting incorrectly your query by removing the parenthesis around
« ON (T1.CustNum = T2.CustNum and T2.OrderNo = 2) » :

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.CustNum = T2.CustNum and
T2.OrderNo = 2) LEFT JOIN Table1 AS T3 ON T1.CustNum = T3.CustNum and
T3.OrderNo = 3) LEFT JOIN Table1 AS T4 ON T1.CustNum = T4.CustNum and
T4.OrderNo = 4
WHERE T1.OrderNo = 1
WITH OWNERACCESS OPTION;

As you can see, Access is very crappy when it comes to complex expression
involving Left Join and/or Subqueries.

The best option for you would be to use a temporary table to build your
result.

 

[Sylvain Lafontaine]

After taking a look at some other posts, just saw/remembering that you can
use an alternate syntax for performing a Left Join on multiples fields that
is more palatable to the graphic designer; so instead of writing:

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 T1 Left JOIN Table1 AS T2 ON (T1.CustNum = T2.CustNum and
T2.OrderNo = 2)) Left Join Table1 as T3 ON (T1.CustNum = T3.CustNum and
T3.OrderNo = 3)) Left Join Table1 as T4 on (T1.CustNum = T4.CustNum and
T4.OrderNo = 4)
Where T1.OrderNo = 1
WITH OWNERACCESS OPTION;

you can write:

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM (([SELECT CustNum, Code, Amt FROM Table1 WHERE OrderNo=1]. AS T1 LEFT
JOIN [SELECT CustNum, Code, Amt FROM Table1 WHERE OrderNo=2]. AS T2 ON
T1.CustNum=T2.CustNum) LEFT JOIN [SELECT CustNum, Code, Amt FROM Table1
WHERE OrderNo=3]. AS T3 ON T1.CustNum=T3.CustNum) LEFT JOIN [SELECT CustNum,
Code, Amt FROM Table1 WHERE OrderNo=4]. AS T4 ON T1.CustNum=T4.CustNum
WITH OWNERACCESS OPTION;

However, on some occasions, particularly if I make some change to the
testing table Table1, Access has sometimes rewrote it under the wrong
following format:

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM (([SELECT CustNum, Code, Amt FROM Table1 WHERE OrderNo=1; ] AS T1 LEFT
JOIN [SELECT CustNum, Code, Amt FROM Table1 WHERE OrderNo=2; ] AS T2 ON
T1.CustNum=T2.CustNum) LEFT JOIN [SELECT CustNum, Code, Amt FROM Table1
WHERE OrderNo=3; ] AS T3 ON T1.CustNum=T3.CustNum) LEFT JOIN [SELECT
CustNum, Code, Amt FROM Table1 WHERE OrderNo=4; ] AS T4 ON
T1.CustNum=T4.CustNum
WITH OWNERACCESS OPTION;

but this form (the previous one, the one that is OK) seems to be a more
stable format than the form that I've used in my previous post.

With this form, by using the following subquery Query2b:

SELECT CustNum, Code, Amt, (Select Count(*) from Table2 as T2 where
T2.IdTable <= T1.IdTable and T2.CustNum = T1.CustNum) AS OrderNo
FROM Table2 AS T1
WITH OWNERACCESS OPTION;

it's possible to write the query that you need:

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM (([SELECT CustNum, Code, Amt FROM (Select * From Query2b WHERE
OrderNo=1) as Table1]. AS T1 LEFT JOIN [SELECT CustNum, Code, Amt FROM
(Select * From Query2b WHERE OrderNo=2) as Table1]. AS T2 ON T1.CustNum =
T2.CustNum) LEFT JOIN [SELECT CustNum, Code, Amt FROM (Select * From Query2b
WHERE OrderNo=3) as Table1]. AS T3 ON T1.CustNum = T3.CustNum) LEFT JOIN
[SELECT CustNum, Code, Amt FROM (Select * From Query2b WHERE OrderNo=4) as
Table1]. AS T4 ON T1.CustNum = T4.CustNum
WITH OWNERACCESS OPTION;

or, if you want to replace Query2b directly with its sql code:

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM (([SELECT CustNum, Code, Amt FROM (Select * From (SELECT CustNum, Code,
Amt, (Select Count(*) from Table2 as T2 where T2.IdTable <= T1.IdTable and
T2.CustNum = T1.CustNum) AS OrderNo
FROM Table2 AS T1) WHERE OrderNo=1) as Table1]. AS T1 LEFT JOIN [SELECT
CustNum, Code, Amt FROM (Select * From (SELECT CustNum, Code, Amt, (Select
Count(*) from Table2 as T2 where T2.IdTable <= T1.IdTable and T2.CustNum =
T1.CustNum) AS OrderNo
FROM Table2 AS T1) WHERE OrderNo=2) as Table1]. AS T2 ON T1.CustNum =
T2.CustNum) LEFT JOIN [SELECT CustNum, Code, Amt FROM (Select * From (SELECT
CustNum, Code, Amt, (Select Count(*) from Table2 as T2 where T2.IdTable <=
T1.IdTable and T2.CustNum = T1.CustNum) AS OrderNo
FROM Table2 AS T1) WHERE OrderNo=3) as Table1]. AS T3 ON T1.CustNum =
T3.CustNum) LEFT JOIN [SELECT CustNum, Code, Amt FROM (Select * From (SELECT
CustNum, Code, Amt, (Select Count(*) from Table2 as T2 where T2.IdTable <=
T1.IdTable and T2.CustNum = T1.CustNum) AS OrderNo
FROM Table2 AS T1) WHERE OrderNo=4) as Table1]. AS T4 ON T1.CustNum =
T4.CustNum
WITH OWNERACCESS OPTION;

and this is only with a maximum of four sales codes for each customer. In
your case, you can reach 15 sales codes; so obviously a much better solution
would be to use a temporary table to build your result by looping on each
column.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

This query is not easy because the same code doesn't repeat for each
CustNum in the same column. The most easy way would be to use a temporary
table to build your result. If you don't want to use a temporary table,
then you should add an order number and use it to build your result using
IIF() statements using a serie of Left Join:

CustNum.......Code.............Amt OrderNo
15893............A4..............15.10 1
15893............BK..............17.10 2
15893............Z4..............19.10 3
16893............TR..............12.10 1
16893............UK..............17.15 2
16893............WR.............22.50 3
16893............YT................5.53 4

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 T1 Left JOIN Table1 AS T2 ON (T1.CustNum = T2.CustNum and
T2.OrderNo = 2)) Left Join Table1 as T3 ON (T1.CustNum = T3.CustNum and
T3.OrderNo = 3)) Left Join Table1 as T4 on (T1.CustNum = T4.CustNum and
T4.OrderNo = 4)
Where T1.OrderNo = 1
WITH OWNERACCESS OPTION;

If IdTable is the primary key of the table, the following query will
automatically create the OrderNo field:

Select CustNum, Code, Amt, (Select Count(*) from Table1 as T2 where
T2.IdTable <= T1.IdTable and T2.CustNum = T1.CustNum) as OrderNo
From Table1 as T1

On SQL-Server, this work perfectly. However, on Access, this doesn't
work; as JET is known to bug when LEFT JOIN are too complex but if you use
a table with the column OrderNo already prepared, it works. Bad new is
that when created, you cannot open this query in the query designer any
more without Access rewriting incorrectly your query by removing the
parenthesis around « ON (T1.CustNum = T2.CustNum and T2.OrderNo = 2) » :

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.CustNum = T2.CustNum and
T2.OrderNo = 2) LEFT JOIN Table1 AS T3 ON T1.CustNum = T3.CustNum and
T3.OrderNo = 3) LEFT JOIN Table1 AS T4 ON T1.CustNum = T4.CustNum and
T4.OrderNo = 4
WHERE T1.OrderNo = 1
WITH OWNERACCESS OPTION;

As you can see, Access is very crappy when it comes to complex expression
involving Left Join and/or Subqueries.

The best option for you would be to use a temporary table to build your
result.

 

[ironwood9 via AccessMonster.com]

Sylvain,
Thank you so much for your help !

This is just a one-time scenario, so poor db design is not an issue - I'll
probably never have to do this again.. - so which one is for the temporary
solution ? I will need to modify the code - I don't think for 15 iterations,
but at least 10.

Steve (Ironwood9)

This query is not easy because the same code doesn't repeat for each CustNum
in the same column. The most easy way would be to use a temporary table to
build your result. If you don't want to use a temporary table, then you
should add an order number and use it to build your result using IIF()
statements using a serie of Left Join:

CustNum.......Code.............Amt OrderNo
15893............A4..............15.10 1
15893............BK..............17.10 2
15893............Z4..............19.10 3
16893............TR..............12.10 1
16893............UK..............17.15 2
16893............WR.............22.50 3
16893............YT................5.53 4

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 T1 Left JOIN Table1 AS T2 ON (T1.CustNum = T2.CustNum and
T2.OrderNo = 2)) Left Join Table1 as T3 ON (T1.CustNum = T3.CustNum and
T3.OrderNo = 3)) Left Join Table1 as T4 on (T1.CustNum = T4.CustNum and
T4.OrderNo = 4)
Where T1.OrderNo = 1
WITH OWNERACCESS OPTION;

If IdTable is the primary key of the table, the following query will
automatically create the OrderNo field:

Select CustNum, Code, Amt, (Select Count(*) from Table1 as T2 where
T2.IdTable <= T1.IdTable and T2.CustNum = T1.CustNum) as OrderNo
From Table1 as T1

On SQL-Server, this work perfectly. However, on Access, this doesn't work;
as JET is known to bug when LEFT JOIN are too complex but if you use a table
with the column OrderNo already prepared, it works. Bad new is that when
created, you cannot open this query in the query designer any more without
Access rewriting incorrectly your query by removing the parenthesis around
« ON (T1.CustNum = T2.CustNum and T2.OrderNo = 2) » :

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.CustNum = T2.CustNum and
T2.OrderNo = 2) LEFT JOIN Table1 AS T3 ON T1.CustNum = T3.CustNum and
T3.OrderNo = 3) LEFT JOIN Table1 AS T4 ON T1.CustNum = T4.CustNum and
T4.OrderNo = 4
WHERE T1.OrderNo = 1
WITH OWNERACCESS OPTION;

As you can see, Access is very crappy when it comes to complex expression
involving Left Join and/or Subqueries.

The best option for you would be to use a temporary table to build your
result.

My table looks like:

[quoted text clipped - 20 lines]

(No cust would have more than 15 sales codes with amounts if that makes a
difference)

 

[Sylvain Lafontaine]

It's not a question of poor db design issue. You need to transpose or
transform the data into multiple columns. However, you have one field which
is not numeric so you can't use the regular method of using the Transform or
Pivot Transformation of Access.

The best bet in your case would be to use a temporary table and build your
data data using VBA code. This is usually what I'm doing myself even when
I'm working on SQL-Server because this is the simplest way of doing a
transposition; especially when the criteria for doing this are complex.

However, if you want to do it one time, then you can take my exemples of
code - which is right for up to 4 customer codes - and augment it to 15
codes.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain,
Thank you so much for your help !

This is just a one-time scenario, so poor db design is not an issue - I'll
probably never have to do this again.. - so which one is for the temporary
solution ? I will need to modify the code - I don't think for 15
iterations,
but at least 10.

Steve (Ironwood9)

This query is not easy because the same code doesn't repeat for each
CustNum
in the same column. The most easy way would be to use a temporary table
to
build your result. If you don't want to use a temporary table, then you
should add an order number and use it to build your result using IIF()
statements using a serie of Left Join:

CustNum.......Code.............Amt OrderNo
15893............A4..............15.10 1
15893............BK..............17.10 2
15893............Z4..............19.10 3
16893............TR..............12.10 1
16893............UK..............17.15 2
16893............WR.............22.50 3
16893............YT................5.53 4

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 T1 Left JOIN Table1 AS T2 ON (T1.CustNum = T2.CustNum and
T2.OrderNo = 2)) Left Join Table1 as T3 ON (T1.CustNum = T3.CustNum and
T3.OrderNo = 3)) Left Join Table1 as T4 on (T1.CustNum = T4.CustNum and
T4.OrderNo = 4)
Where T1.OrderNo = 1
WITH OWNERACCESS OPTION;

If IdTable is the primary key of the table, the following query will
automatically create the OrderNo field:

Select CustNum, Code, Amt, (Select Count(*) from Table1 as T2 where
T2.IdTable <= T1.IdTable and T2.CustNum = T1.CustNum) as OrderNo
From Table1 as T1

On SQL-Server, this work perfectly. However, on Access, this doesn't
work;
as JET is known to bug when LEFT JOIN are too complex but if you use a
table
with the column OrderNo already prepared, it works. Bad new is that when
created, you cannot open this query in the query designer any more without
Access rewriting incorrectly your query by removing the parenthesis around
« ON (T1.CustNum = T2.CustNum and T2.OrderNo = 2) » :

SELECT T1.CustNum, T1.Code, T1.Amt, T2.Code, T2.Amt, T3.Code, T3.Amt,
T4.Code, T4.Amt
FROM ((Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.CustNum = T2.CustNum and
T2.OrderNo = 2) LEFT JOIN Table1 AS T3 ON T1.CustNum = T3.CustNum and
T3.OrderNo = 3) LEFT JOIN Table1 AS T4 ON T1.CustNum = T4.CustNum and
T4.OrderNo = 4
WHERE T1.OrderNo = 1
WITH OWNERACCESS OPTION;

As you can see, Access is very crappy when it comes to complex expression
involving Left Join and/or Subqueries.

The best option for you would be to use a temporary table to build your
result.

My table looks like:

[quoted text clipped - 20 lines]

(No cust would have more than 15 sales codes with amounts if that makes
a
difference)

 

[ironwood9 via AccessMonster.com]

Sylvain,
I'm not following 100% - Are you saying my sample table above is to be my
"Table1" ?

It's not a question of poor db design issue. You need to transpose or
transform the data into multiple columns. However, you have one field which
is not numeric so you can't use the regular method of using the Transform or
Pivot Transformation of Access.

The best bet in your case would be to use a temporary table and build your
data data using VBA code. This is usually what I'm doing myself even when
I'm working on SQL-Server because this is the simplest way of doing a
transposition; especially when the criteria for doing this are complex.

However, if you want to do it one time, then you can take my exemples of
code - which is right for up to 4 customer codes - and augment it to 15
codes.

Sylvain,
Thank you so much for your help !

[quoted text clipped - 70 lines]

 

[ironwood9 via AccessMonster.com]

Sylvain,
I'm not understanding what's the Table1, Table2, and IDtable ? I only gave a
small portion of sample data - what table name are you giving my sample data,
and what do the other 2 tables consist of ? I think one is just an alias,
but I'm not sure.
Steve

Sylvain,
Thank you so much for your help !

This is just a one-time scenario, so poor db design is not an issue - I'll
probably never have to do this again.. - so which one is for the temporary
solution ? I will need to modify the code - I don't think for 15 iterations,
but at least 10.

Steve (Ironwood9)

This query is not easy because the same code doesn't repeat for each CustNum
in the same column. The most easy way would be to use a temporary table to

[quoted text clipped - 54 lines]

 

[Sylvain Lafontaine]

IdTable is the primary key of the table. You didn't give any information
about the primary key used for your table - every table should have one to
uniquely identify each record - so I used IdTable; which is a common
convention.

Table1 and Table2 are simply your tblSales; the first one with a
supplemental column called OrderNo to identify the order of which each
customer code (A4, BK, etc.) must appear for each column from 1 to 15 after
the transposition. When you don't have such a column, the query for doing
your transformation is more complicated because it must find this order by
itself; hence the use of the subquery Query2b in some of my examples.

These examples are staightforward; if you have trouble reading them, you
should really go with the use of temporary table to compute your
transformation; much more easier then to try understanding a query with
multiples Left Join and subqueries.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sylvain,
I'm not understanding what's the Table1, Table2, and IDtable ? I only
gave a
small portion of sample data - what table name are you giving my sample
data,
and what do the other 2 tables consist of ? I think one is just an alias,
but I'm not sure.
Steve

Sylvain,
Thank you so much for your help !

This is just a one-time scenario, so poor db design is not an issue - I'll
probably never have to do this again.. - so which one is for the temporary
solution ? I will need to modify the code - I don't think for 15
iterations,
but at least 10.

Steve (Ironwood9)

This query is not easy because the same code doesn't repeat for each
CustNum
in the same column. The most easy way would be to use a temporary table
to

[quoted text clipped - 54 lines]

(No cust would have more than 15 sales codes with amounts if that makes
a
difference)