Crosstab Query Column heading help

[maeza001]

I need to create a crosstab query in Access 97 that will basically move data in different rows to multiple colums in the same row.
Each [MemberID] has multiple dates of Service ([DOS]) for a particular type of service ([svctype]), such as "A", "B", etc.. I want to create a crosstab that will give me the row heading [memberID] and then column headings for each instance of [svctype] "A" in order of [DOS]. So that it would look like this:

MemberId A1 A2 A3 A4
123456 1/2/06 3/8/06 4/21/06 8/7/06
234567 5/4/06 6/8/06

I'm kind of new to Access programming and am kind of learning as I go. Is there any conceiveable way to do this?

~HM

 

[PHV]

Have you tried to follow the CrossTab query wizard ?

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

 

[maeza001]

Yes, I have. This will only allow me to get Aggregate values such as Count, sum, min or max for the the [DOS] value and will only give me the Group By option for the column heading [svctype]. So if I use count of [DOS] I end up with:

MemberID A
123456 4
234567 2

I'm doing this because I need to be able to analyze the data based the elapsed time between DOS's. I'm not sure how to do that without first getting the dates all on the same row.

 

[dhookom]

You can set the DOS to the First of DOS for the value. Use the svctype field as the column heading.

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]

 

[maeza001]

dhookom,

Thanks for the suggestion, but that would still only give me one column of data. Based on my example, if I did that, I would get the MemberID as row header, the colunn header "A", and the First DOS. But what I need is a column header "A1", containing the Min DOS, then a column header "A2", containing the next highest DOS, then a column header "A3", etc. I would need it to generate as many columns as needed for svctype "A" based on the highest number of unique DOS's for an existing member.
I hope I'm explaining myself clearly. I know what I need as the output, but I may not be explaining it correctly.

Thanks!

 

[dhookom]

You need to calculate the 1, 2, 3, ... to use a the column heading. This can be done very slowly with a column heading expression like:
ColHead: [svctype] & DCount("*","tblNoNameGiven","svctype='" & [scvtype] & "' AND [MemberID]=" & [MemberID] & " AND [DOS]<=#" & [DOS] & "#")



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]