Moving data from rows to colums 1

[Aietoe]

Hi!
Using Access97, i have a table that goes like this:

Cust# Prod#
_______________

0001 1234
0001 2345
0001 3456
0002 4321
0002 5432
0002 6543

and i would like to create a table like this:

Cust# Prod1 Prod2 Prod3
________________________________

0001 1234 2345 3456
0002 4321 5432 6543

Is there an easy way to do this?

Thanks

Aietoe

 

[RobertT687]

Create a Crosstab Query, then create a Make Table query using the Crosstab query as the data source.

 

[Aietoe]

Hi RobertT687

Thanks for your help, but here are the results i get so far:

Cust# #ofProd Prod#1234 Prod#2345 Prod#3456
_____________________________________________________
0001 3 1234 2345 3456
0002 1 1234
0003 1 2345
0004 1 1234
0005 1 3456

and here's what i'm looking for:

Cust# # of Prod Prod#1 Prod#2 Prod#3
_____________________________________________________
0001 3 1234 2345 3456
0002 1 1234
0003 1 2345
0004 1 1234
0005 1 3456

If you can give me any hints, i have tried all kind of things since your reply yesterday, and i'm out of ideas.

Thanks!

Aietoe

 

[ldandy]

When you made the crosstab query, I guess you choose as pivot the Prod column and then you grouped by Cust. This way you will end up with a table that will have as many columns as the number of distinct values in the Prod column plus the columns you have in your SELECT query.

The problem would be much easier to solve if the original table had a new column, let's say ProdPos that holds a value that numbers the products for each customer (so for customer 0001 will be 1 for first record, 2 for the second record, ... and then for cust 0002 will start again from 1, ...). Then the crosstab should be done having the pivot this new column. Then you will have the desired result.

Now the problem is how do we update this new column because we do not have to go and change it manually. I think we might get it with a query like this:

SELECT Cust, Prod, DCount('Prod', 'CustProdTable', 'Cust=' & Cust & ' AND Prod <= ' & Prod) AS ProdPos
FROM CustProdTable

Then you make the crosstab query based on this query (you save this, give it a name and use it as it were a table) with the pivot ProdPos.

I'm not sure it works because I haven't tested, but it might help. It won't be very fast because of DCount function.

Danny.

 

[Aietoe]

Hi Danny,
Thanks for your help. I have tried to execute the following query:

SELECT [TABLE 00IF].Cust, [TABLE 00IF].Prod,DCount('Prod','Table 00IF','Cust=' & [Cust] & ' AND Prod <= ' & [Prod]) AS ProdPos

to create the new colum to get the product count, but unfortunatly, it gives me an error message(#ERROR).

I do not see where the problem is... any Idea?

Thanks

Aietoe

 

[ldandy]

One problem could be if either Cust or Prod fields are not numeric, then the condition for DCount should be changed a little bit. Another reason of the error could be that either Cust or Prod are null.

But since Cust is '0001' in your example, I might suppose it is a text. If that's the case you should change the query as follows:

SELECT [TABLE 00IF].Cust, [TABLE 00IF].Prod,DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= &quot; & [Prod]) AS ProdPos
FROM [TABLE 00IF]

The idea is that DCount is a kind of subquery and Access builds the conditon for this subquery. In the previous query the built conditon for one customer looked like:
Cust = 0001 AND Prod <= 1234. With the new query it should look like: Cust = '0001' AND Prod <= 1234 which is the correct form. If Prod is text as well you should change further the query after the same pattern.

 

[jacksonmacd]

Here's an off-the-wall suggestion that may work if you just want it for reporting purposes. Create the crosstab query. Make another query that concatenates all the fields into a single string variable, with appropriate spacing between fields. Trim all the leading blanks.

Obviously, this won't work if you want to do some subsequent processing on the data.

 

[Aietoe]

Yeah... almost there!

It works fine with the cust field being a text and the prod field being numeric... but....
... the prod field is a &quot;date&quot;... i tried with this field as numeric or alpha... no luck

One more and last idea(hopefully)?

Thanks


Aietoe

 

[ldandy]

It's strange that Prod field is a date. I thought it was the product number. A date field should be passed to the WHERE clause surrounded by '#'. So the DCount will be:

DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= #&quot; & [Prod] & &quot;#&quot

One more thing. If for the same Cust you have more Prod's with the same value, then the Table of DCount sould be a query that selects only the distinct values, otherwise you will end up with missing ranks (so instead of 'Table 00IF' you should have the name of that query).

Hope this helps,
Danny.

 

[Aietoe]

Hi Danny,

What do you mean by «A date field should be passed to the WHERE clause surrounded by '#'» ?

Aietoe

 

[Aietoe]

Hi Danny,

After looking at your solution again, i believe that «A date field should be passed to the WHERE clause surrounded by '#'» explain your changes to the DCOUNT....

So i did make the changes and here are the results i get:


CUST Prod(Date) ProdPos2
A1234 01-02-13 6
A1234 01-05-01 0
A1234 01-06-05 6
A1234 01-06-19 6
A1234 01-10-02 6
A1234 01-10-23 6
B2345 01-02-28 3
B2345 01-04-24 3
B2345 01-08-28 3
C3456 01-02-13 3
C3456 01-02-28 3
C3456 01-03-13 3
D4567 00-12-06 1
D4567 01-01-24 4
D4567 01-03-21 4
D4567 01-06-06 4

I guess i need a little more help....

Aietoe

 

[ldandy]

Aietoe,

I have created a table with two columns and used the query below:

SELECT [TABLE 00IF].Cust, [TABLE 00IF].Prod, DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= #&quot; & [Prod] & &quot;#&quot AS ProdPos
FROM [TABLE 00IF];

I got the following results:

Cust Prod ProdPos
A1234 2/13/2001 1
A1234 5/1/2001 2
A1234 6/5/2001 3
A1234 6/19/2001 4
A1234 10/2/2001 5
A1234 10/23/2001 6
B2345 2/28/2001 1
B2345 4/24/2001 2
B2345 8/28/2001 3
C3456 2/13/2001 1
C3456 2/28/2001 2
C3456 3/13/2001 3
D4567 12/6/2000 1
D4567 1/24/2001 2
D4567 3/21/2001 3
D4567 6/6/2001 4

So if the dates are different for the same Cust then you should not get the same ProdPos. Neither should you get 0 as ProdPos because for every row there is at least one record that is less or equal ('<=') it is the row itself. So please use the query above with the same data and see if you get the same result I did.

Danny.

P.S. Don't forget what I said in my previous post. If you have for the same Cust two identical Prod then DCount should be modified as I said there.

 

[Aietoe]

Hi Danny,

Here is my table:
Cust Prod
A1234 01-02-13
A1234 01-05-01
A1234 01-06-05
A1234 01-06-19
A1234 01-10-02
A1234 01-10-23
B2345 01-02-28
B2345 01-04-24
B2345 01-08-28
C3456 01-02-13
C3456 01-02-28
C3456 01-03-13
D4567 00-12-06
D4567 01-01-24
D4567 01-03-21
D4567 01-06-06

Cust is defined as TEXT and Prod is defined as DATE.

Here is my query:

SELECT [TABLE 00IF].Cust, [TABLE 00IF].Prod, DCount('Prod','Table 00IF',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= #&quot; & [Prod] & &quot;#&quot AS ProdPos
FROM [TABLE 00IF]
ORDER BY [TABLE 00IF].Cust, [TABLE 00IF].Prod;

And here are the results:

Cust Prod ProdPos
A1234 01-02-13 6
A1234 01-05-01 0
A1234 01-06-05 6
A1234 01-06-19 6
A1234 01-10-02 6
A1234 01-10-23 6
B2345 01-02-28 3
B2345 01-04-24 3
B2345 01-08-28 3
C3456 01-02-13 3
C3456 01-02-28 3
C3456 01-03-13 3
D4567 00-12-06 1
D4567 01-01-24 4
D4567 01-03-21 4
D4567 01-06-06 4

I don't see where the difference is, but the results are definitely different.......

Aietoe

 

[Aietoe]

Hi Danny,

I continued to try all sort of things...
I created a new table entering the dates as follow:
2000-12-12... 2001-03-31... etc...
The format of these dates at the screen was changed and they appeared as: 00-12-12... 01-03-31...etc. BUT IT WORKED....
So i guess it as to do with the format of my dates, don't you think?

Aietoe

 

[ldandy]

Can you change the date format of your table? I don't know why it doesn't work with the date format you have.

 

[Aietoe]

I Danny,

I changed the format of may dates to yyyy-mm-dd and then to mm-dd-yyyy........

Look at the following results.... now, i'm really confuse(??????????????????????)
Query:
SELECT Table3.Cust, Table3.Prod, DCount('Prod','Table3',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= # &quot; & [Prod] & &quot; #&quot AS ProdPos
FROM Table3
ORDER BY Table3.Cust, Table3.Prod, DCount('Prod','Table3',&quot;Cust='&quot; & [Cust] & &quot;' AND Prod <= # &quot; & [Prod] & &quot; #&quot;

Table:
Cust Prod
A1234DiffDay 01-01-2000
A1234DiffDay 01-05-2000
A1234DiffDay 01-10-2000
A1234DiffDay 01-15-2000
A1234DiffDay 01-20-2000
A1234DiffDay 01-25-2000
B2345DiffMonth 01-01-2001
B2345DiffMonth 02-01-2001
B2345DiffMonth 03-01-2001
B2345DiffMonth 04-01-2001
B2345DiffMonth 05-01-2001
B2345DiffMonth 06-01-2001
C3456DiffYear 01-01-1997
C3456DiffYear 01-01-1998
C3456DiffYear 01-01-1999
C3456DiffYear 01-01-2000
C3456DiffYear 01-01-2001
C3456DiffYear 01-01-2002

Cust Prod ProdPos
A1234DiffDay 01-01-2000 1
A1234DiffDay 01-05-2000 2
A1234DiffDay 01-10-2000 3
A1234DiffDay 01-15-2000 4
A1234DiffDay 01-20-2000 5
A1234DiffDay 01-25-2000 6
B2345DiffMonth 01-01-2001 1
B2345DiffMonth 02-01-2001 1
B2345DiffMonth 03-01-2001 1
B2345DiffMonth 04-01-2001 1
B2345DiffMonth 05-01-2001 1
B2345DiffMonth 06-01-2001 1
C3456DiffYear 01-01-1997 1
C3456DiffYear 01-01-1998 2
C3456DiffYear 01-01-1999 3
C3456DiffYear 01-01-2000 4
C3456DiffYear 01-01-2001 5
C3456DiffYear 01-01-2002 5

Aietoe

 

[Aietoe]

Hi Danny,

I think i found a solution.... not too elegant, but it works...)

I copy my table changing the date format to numeric; then execute the dcount(whitch works perfectly with numerics) and then copy back my table translating the numeric date to its original format..... Bingo!

Thanks a lot anyway for your help and patience. For sure, i wouldn't have a solution by now without your help.

Aietoe