RE: I can't get data into a column type format needed
- From: KARL DEWEY <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 28 Mar 2007 16:28:02 -0700
Post sample data produced by query3 with field names.I have created a query3 which has all of the data that I want to work with
--
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
- Follow-Ups:
- Prev by Date: Re: Memo Field on Report causes Second Page
- Next by Date: Add Collapsible data to report or form
- Previous by thread: Re: Sums from Multiple Queries on one Report
- Next by thread: RE: I can't get data into a column type format needed
- Index(es):
Relevant Pages
|
|
