Setting Crosstab Dynamic Columns 1

[acjim]

Hi,

I have data like this:

Pat_Number Code Result CRN Spec_Date
693947 HB 9999 976498 19/09/2003
693947 HB 9.7 976498 19/09/2003
693947 HB 8.8 976498 19/09/2003
693947 HB 8 976498 19/09/2003
693947 HB 16.4 976498 19/09/2003
693947 HB 8.5 976498 19/09/2003

and I want it to be like this:

Pat_Number Code Spec_Date CRN Result1 Result2 Result3...
693947 HB 19/09/2003 976498 8 8.5 8.8

(the 9999 result is a code for an error)

I can get a crosstab to give me as above but with the column titles as the result, i.e. 8|8.5|8.8 etc

How can I get the query to order and number the results?

Many thanks

Jim

 

[dhookom]

To simplify this, I would first create a query that numbers the results 1, 2, 3,... The problem is that there are no values in your display that can be used to establish the order. Which record is 1? Is the lowest value of Result the number 1 record? What would you expect if there is a tie of the Result field?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[acjim]

Duane,

You were the person I was hoping to find!

I've actually worked around the problem now. By doing what you suggest, I ranked the results - made a table and then a crosstab from that table.

This worked but is very clunky and means I've got to build a macro for the process to be user launched.

Do you have any tips on using Alias's in Crosstab queries? I've used them frequently in this piece of work for finding closest date, ranking etc BUT when I've tried to combine them all into a final summary query, Access Errors out saying it cannot find the Alias. - weird?

Thanks for your response

Jim

 

[dhookom]

Crosstabs don't like subqueries. I would have suggested using the very slow DCount() function.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[JonFer]

If you use a join instead of a subquery to do the ranking, Access will not choke on it in a crosstab.

The ranking query would be like this:

Select a.PartNumber, a.Result, Count(b.Result) as Rank
From Table1 as a Inner Join Table1 as b On
      A.PartNumber = b.PartNumber
Where b.Result<=a.Result

It assumes that Result values are not duplicated within a PartNumber.


John

http://www.interautomateconsulting.com

 

[acjim]

JonFer,

I needed to add a GROUP BY to make your query work. But it does, and is definately smoother than using SubQueries.

Thanks
Have a little star for your extra help.
jim

 

[JonFer]

You'd think I would remember the GROUP BY now...

John

http://www.interautomateconsulting.com

 

[jdegeorge]

acJim

If you don't mind, can you post your final code for this solution? I'm intrigued.

Jim DeGeorge

 

[PHV]

Select a.PartNumber, a.Result, Count(b.Result) as Rank
From Table1 as a Inner Join Table1 as b On
A.PartNumber = b.PartNumber
Where b.Result<=a.Result
Group By a.PartNumber, a.Result

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[acjim]

Hi,

As PHV has it, but with additional link between dates to keep the ranking contained to a particular date:

SELECT tblHbInput.Pat_Number, tblHbInput.Code, tblHbInput.Spec_Date, tblHbInput.CRN, tblHbInput.Result, Count(tblHbInput.Pat_Number) AS Rank

FROM tblHbInput INNER JOIN tblHbInput AS tblHbInput_1 ON (tblHbInput.Pat_Number = tblHbInput_1.Pat_Number)AND (tblHbInput.Spec_Date = tblHbInput_1.Spec_Date)

WHERE (((tblHbInput_1.Result)<=[tblHbInput].[Result]))

GROUP BY tblHbInput.Pat_Number, tblHbInput.Code, tblHbInput.Spec_Date, tblHbInput.CRN, tblHbInput.Result;

ta
jim