Cross Tab - Rank Query? Help

[TMcGuireFCC]

I am seeing lots of questions about this, and someone said to search based upon "Rank Query" but I cannot find one. I am good at Access, but not the SQL behind the scenes.

I have a large list of Clients, where each client has from 1 - 10 Service People. I need a cross-tab-like query that Transposes the a vertical list of Clients with recurring clients with unique servicers to a list of 1 Client, and the service people in Columns 2-11

ClientA ServicerA
ClientA ServicerB
ClientB ServicerC
ClientC ServicerA
ClientC ServicerC

changes to

ClientA ServicerA ServicerB
ClientB ServicerC
ClientC ServicerA ServicerC

Just cannot figure it out.

The ranking would work, but otherwise it generates using query after query and Firs and Last Aggregate many times.

Can someone advise an easy way?

TeriM

 

[dhookom]

It would help to know your actual table and field names. However, your crosstab would have a SQL view like:

TRANSFORM First(qForTeriM.Servicer) AS FirstOfServicer
SELECT qForTeriM.Client
FROM qForTeriM
GROUP BY qForTeriM.Client
PIVOT "Servicer" & DCount("*","qForTeriM","Client=""" & [Client] & """ AND Servicer<=""" & [Servicer] & """");

Depending on the number of records in your tables, this might be very slow.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[TMcGuireFCC]

Duane,

hmmm... Unless I am missing something, it did not work.
Primary field is Clients_ID and Serv1name is the servicer field name.

It is telling me that I have a syntax error in the PIVOT clause.

TRANSFORM first([Policies Active Today Servicers].serv1name) AS firstofserv1name
SELECT [Policies Active Today Servicers].clients_id
FROM [Policies Active Today Servicers]
GROUP BY [Policies Active Today Servicers].CLIENTS_ID
PIVOT "[serv1name]" & Dcount("*","[Policies Active Today Servicers]","CLIENTS_ID=""" & [CLIENTS_ID] & """ AND [serv1name] <=""" & ( [serv1name] & """");

 

[lespaul]

Is clients ID a number or text field?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[TMcGuireFCC]

Client_ID is a number field.

 

[dhookom]

My bad. It looked like text in your first message. If serv1name is text, try:

TRANSFORM first([Policies Active Today Servicers].serv1name) AS firstofserv1name
SELECT [Policies Active Today Servicers].clients_id
FROM [Policies Active Today Servicers]
GROUP BY [Policies Active Today Servicers].CLIENTS_ID
PIVOT "[serv1name]" & Dcount("*","[Policies Active Today Servicers]","CLIENTS_ID=" & [CLIENTS_ID] & " AND [serv1name] <=""" & ( [serv1name] & """");

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[TMcGuireFCC]

still does not like it.
a number field does not need the [] or "" BUT a text field does?
and why all the extra "s?
I am in Access 2003, not 07 - does that make a difference?

 

[dhookom]

The []s are required for any object name (ie: field or table name) that contains spaces. "Policies Active Today Servicers" contains 3 spaces and will need the []s almost every where it is used. ManyOfUsVeteranDevelopersNeverCreateNamesWithSpaces.

Text data types require delimiting with single or double quotes. Dates require delimiting with "#" (Access/JET queries) and numeric fields don't require any delimiter.

I prefer to double-up my [red]"[/red] like:

& " AND [serv1name] <=""" & ( [serv1name] & """");

This could also be written:

& " AND [serv1name] <='" & ( [serv1name] & "'");

The second method would have an issue with a server1name with an embedded [red]'[/red] like O[red]'[/red]Malley.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

Parenthesis problem in the PIVOT clause.

TRANSFORM First(serv1name) AS firstofserv1name
SELECT Client_ID
FROM [Policies Active Today Servicers]
GROUP BY Client_ID
PIVOT "serv1name" & Dcount("*","[Policies Active Today Servicers]","Client_ID=" & Client_ID & " AND serv1name<='" & serv1name & "'");

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TMcGuireFCC]

thank you all for your help. Yes it takes forever to make the table, but it workled after removing that last (.

I guess it is time to teach myself SQL.....