Cross Tab Query

[bigbore50]

Hello

I hope you can help


What i have is a query that shows

Primary Number Date
12345 12/15/06
12345 11/2/06
12345 1/5/06
12345 10/12/05

No what i want to do is make a table that looks like

Primary number Date1 Date2 Date3 Date4
12345 12/15/06 11/2/06 1/5/06 10/12/05


Is this Possible??

Thanks for your help

 

[giorgio rancati]

Hi,

make this query
----
SELECT T1.[Primary Number],
T1.Date,
Count(*) AS Position
FROM

T1
INNER JOIN

T2
ON T1.[Primary Number]=T2.[Primary Number]
AND T1.Date<=T2.Date
GROUP BY T1.[Primary Number], T1.Date
----
and save it with name "qryPosition"
it does this result
----
Primary Number Date Position
12345 10/12/05 4
12345 01/05/06 3
12345 11/02/06 2
12345 12/15/06 1
----

then make the Cross Tab Query
----
TRANSFORM Min(Date) AS MinDiDate
SELECT [Primary Number]
FROM [qryPosition]
GROUP BY [Primary Number]
PIVOT "Date" & [Position];

 

[bigbore50]

WOW,

That is different, I never saw anything like that. Thanks a lot

Can i do this with all queries?
it is kind of like adding an AUTONUMBER field
Correct?
Is this how i would add an AutoNumber Field to my queries?

Thanks a lot

Hi,

make this query
----
SELECT T1.[Primary Number],
T1.Date,
Count(*) AS Position
FROM

T1
INNER JOIN

T2
ON T1.[Primary Number]=T2.[Primary Number]
AND T1.Date<=T2.Date
GROUP BY T1.[Primary Number], T1.Date
----
and save it with name "qryPosition"
it does this result
----
Primary Number Date Position
12345 10/12/05 4
12345 01/05/06 3
12345 11/02/06 2
12345 12/15/06 1
----

then make the Cross Tab Query
----
TRANSFORM Min(Date) AS MinDiDate
SELECT [Primary Number]
FROM [qryPosition]
GROUP BY [Primary Number]
PIVOT "Date" & [Position];
----
it does this result
----
Primary Number Date1 Date2 Date3 Date4
12345 12/15/06 11/02/06 01/05/2006 10/12/2005
----

Bye
--
Giorgio Rancati
[Office Access MVP]

Hello

I hope you can help


What i have is a query that shows

Primary Number Date
12345 12/15/06
12345 11/2/06
12345 1/5/06
12345 10/12/05

No what i want to do is make a table that looks like

Primary number Date1 Date2 Date3 Date4
12345 12/15/06 11/2/06 1/5/06 10/12/05


Is this Possible??

Thanks for your help

 

[giorgio rancati]

Hi,
the [Position] field is a Rank partitioned by [Primary Number] field and
ordered by date desc.
data example
----
Primary Number Date Position
12345 10/12/05 4
12345 01/05/06 3
12345 11/02/06 2
12345 12/15/06 1
54328 12/06/05 4
54328 03/08/06 3
54328 09/02/06 2
54328 11/12/06 1

 

[bigbore50]

That works GREAT
Thanks a lot