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

[Dale]

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

 

[KARL DEWEY]

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.

 

[Dale]

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]

Post the SQL for query3 so I can see the relationship between the tables.

 

[Dale]

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]

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

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

Post the SQL for query3 so I can see the relationship between the tables.

 

[Dale]

I went through and added that and it looks good with the exception of one
extra line. I seem to have a row that is all blank. How can I get rid of it?
I have a feeling what may have caused it is when I had to link the code field
from the original query to the standards table so that all codes would list.
I did a join type of 2 to show all records from standards(the code list) and
only those from query3 where they are equal. When I originally created it if
I did not do this it would not show the unused code list until I linked them
the way I did. Should I do something different and then add the statement you
said.

Thanks,
Dale

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

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

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


:

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

:

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


:

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

 

[Dale]

forget the last response I made, I removed the join type and it worked
beautifully. I then started on the report and I am closer now. The only
problem I have now is the column formatting. I did manage to get the labels
to just show one time instead of across all lables. I found someone else who
wanted to do this with labels and there was a link to MS about it. The only
problem I am having now is getting the columns to work correctly. I first
started with the Autoreport. Everything is in details. I set the column to 9
columns. When I do this the limited data seems fine. However, if I change the
column count to something like 4 a small problem exist. The first 4 columns
show ok on the first page then the first two columns on the second page
repeated the supervisor,sc,client. The data that belonged to it seem to just
go under the second column on the second page It is as if it just created the
first column headings for no reason. Any ideas? Also thank you very much for
all of your help so far.

Thanks,
Dale

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

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

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


:

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

:

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


:

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

 

[Dale]

add to my last post, it also post the duplicate on the first page as well. It
seems every page the 1st two column headings are repeated.

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

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

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


:

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

:

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


:

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