RE: I can't get data into a column type format needed

Tech-Archive recommends: Speed Up your PC by fixing your registry

Add to your query like this --
PIVOT Standards.Indicator IN("Code1", "Code2", "Code3");

Change the "Code1" etc. to your codes and make a complete list in the order
you want your column labels.
--
KARL DEWEY
Build a little - Test a little


"Dale" wrote:

Here is the original:
SELECT Table1.code, Count(Table1.code) AS CountOfcode,
Clients.ClientLastName, ISCs.ISCLastName, Supervisors.LaName, Table1.date
FROM (Supervisors RIGHT JOIN (ISCs RIGHT JOIN Clients ON ISCs.[ISC ID] =
Clients.[ISC ID]) ON Supervisors.ID = ISCs.[Supervisor ID]) INNER JOIN Table1
ON Clients.[Client ID] = Table1.clientid
GROUP BY Table1.code, Clients.ClientLastName, ISCs.ISCLastName,
Supervisors.LaName, Table1.date;

When trying to mess with a cross tab that gets close to what I want here is
the code:
TRANSFORM Count(Query3.CountOfcode) AS CountOfCountOfcode
SELECT Query3.LaName, Query3.ISCLastName, Query3.ClientLastName
FROM Standards LEFT JOIN Query3 ON Standards.Indicator = Query3.code
GROUP BY Query3.LaName, Query3.ISCLastName, Query3.ClientLastName
PIVOT Standards.Indicator;

I almost got it to kind of work using the cross tab but it would always add
a blank line because I had to make the indicator(code) relationship so that
there would be code columns available for all fields in the report.

Thanks,
Dale


"KARL DEWEY" wrote:

Post the SQL for query3 so I can see the relationship between the tables.
--
KARL DEWEY
Build a little - Test a little


"Dale" wrote:

When query3 is open in design view it shows the 4 tables I have linked to get
the data. The tables are Table1, Clients, SCs, Supervisors

The actual fields in query3 are:
From table1
code, CountOf: code, date

From Clients table
First Name, Last Name

From SCs
First Name, Last Name

From Supervisors
First Name, Last Name

When it is run based on the date I have entered the results look similar to
this going accross:

CODE Count First Last First Last First Last
02A03 1 John Doe Serv Crd1 Super visor1
02A03 2 Dale Doe Serv Crd1 Super visor1
02A03 1 Jane Doe Serv Crd2 Super visor3
02A04 1 Dale Doe Serv Crd1 Super visor1
02A04 4 Jane Doe Serv Crd2 Super visor3
02B02 1 John Smith Serv Crd4 Super visor4

If I run the query and then set it to pivot table I can make it look similar
to how I want but because I need to have it in a certain type look I will
need put it in a report because I have to do additional calculations and also
may have to do some variations of sorting. Also in the report I would
probably somehow have to make the codes as labels or do something different
because there will be cases where no one received a code but I need the
report to show the code and zeros listed under the respective columns to
indicate there was nothing.

Thanks,
Dale

"KARL DEWEY" wrote:

I have created a query3 which has all of the data that I want to work with
Post sample data produced by query3 with field names.
--
KARL DEWEY
Build a little - Test a little


"Dale" wrote:

I have done something similar to this before but
somehow it is not quite working for me in the format I
have now. I have a table named table1 which contains
the following fields:
date, code, clientid

I also have other tables which contain other data like
Supervisornames, and Service Coordinator Names, and
client information. The table1 is what is storing the
results of a form.

I have created a query3 which has all of the data that
I want to work with such as the code, how many times
that code occurred for that client, the SC name of the
client, and the supervisor name of that SC.

What I am trying to accomplish is to create a report
that list all possible codes available at the left.
The codes will look something like 02A03, 02A04,
02A05, and so on.

At the top on the first row of lines I want it to list
the Supervisor Names.
The next line I want it to list the name of the
service coordinators and have them listed
somewhatunder the Supervisor they belong.
The next line will list the client names under the
Service Coordinator they belong

For example,
Sypvsr represents the supervisor name
SC represents the service coordinator name
C represents the client name in the following:

Supvsr1 Supvsr2 Supvsr3
SC1 SC1 SC2 SC3 SC4 SC5 SC6 SC6 SC7 SC8
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10


Then as mentioned earlier I want the codes listed in
one column at the left. Out to the right it will list
a total count of the number of times that code occurs
for that client.

something like this

Suprvsr1 Supvsr2
ServiCJane ServiCBob ServiCAngie
John Jane Martha
A2A03 0 3 1
A2A04 0 1 1
A2A05 1 0 0
A2A06 6 3 1



I tried to use a Pivot table but I may not have been
doing something right because it just did not format
properly. It came close but I believe it still needs
to be in a report. One issue on the pivot table was if
a code did not exist in the data, it would not appear
on the left. The code also appeared as 1,2, 3, 4
rather then the actual code. The other thing it did
not do is show zeros when people did not have
anything. Even if I got the pivot table to format the
data like I wanted to I was not sure how to get that
to the report because I also have to do other
calculations like count the total number of possible
code responses and divide that by the actual number as
well as show percentages. I believe I can get those
formulas ok, it is just the other column formatting I
am having problems with.

Thanks,
Dale

.

Relevant Pages