Re: transpose results

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)


"ironwood9 via AccessMonster.com" <u12024@uwe> wrote in message
news:906f68fefc39d@xxxxxx
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)

Sylvain Lafontaine wrote:
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)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200901/1



.

Relevant Pages

  • Re: Report shows null values, how to convert to a zero
    ... Open the report in design view, and set the Format property of this text box ... the query in SQL View, and add Nzaround the ... TRANSFORM SumAS SumOfQuantity ...
    (microsoft.public.access.reports)
  • Re: Plotting Scientific Experimental Data in Access
    ... There is Transform query available under Access that will transfor the data ... Check for TRANSFORM in PIVOT int the Access help. ... I don't know Microsoft Chart, so I cannot help you any further. ... > is no transpose function, and I would need to specify the sample number ...
    (microsoft.public.access.forms)
  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query Design View is Slow to Open
    ... same query takes about 20 minutes to open in datasheet view. ... took only seconds to open in both design view and datasheet view. ... The troublesome queries are based entirely on local tables (i.e. no ... HOUSEHOLD_PersonalInfo opens slowly in design ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)
Quantcast