|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hello All:
I have a database of clients, and I need to generate quarterly reports.
I have a field called "Successful at Discharge", where "Dry" and "Cured" are
two of the options. I also have a "Death" and "Other Complication" as open
text fields.
The report needs to contain (disaggregated by quarter):
1. # clients successful at discharge (either "dry" or "cured")
2. # clients with experiencing either "Death" or "Other Complication", as
well as # clients with each. (although rare, a client could experience both)
3. The above figures divided over total records for a percentage
I have the table called [VVF], then a query based on the table called
[all query], and a report based on the query that dispays it nicely for
printing.
Ideally, I would like to be able to see a breakdown such as:
Q1 #successful #death #other. #total complications #total clients
Q2 #successful #death #other. #total compliations #total clients
Q3 #successful #death #other. #total comp. #total
clients
somewhat like using the "count" function in pivot table, except in pivot
table, I cannot isolate a count for the field "successful at discharge" that
includes on "Dry" and "Cured".
Please help!
Alice
|
|
Ice,
Break the Successful at Discharge values into two new fields called something Dry and
Cured.
In the query behind your pivot table, create two calculated columns. (remove the
Successful at Discharge field)
Dry : IIF([Successful at Discharge] = "Dry", 1, 0)
Cured : IIF([Successful at Discharge] = "Cured", 1, 0)
Now use those two fields in your report.
[Quoted Text] > Q1 #Dry #Cured #death #other. #total complications #total clients
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
There are 10 types of people in the world.
Those who understand binary, and those who don't.
"Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
news:3152BD0F-2DF3-43D3-9D3C-BB2A7F72C6AC[ at ]microsoft.com...
> Hello All:
>
> I have a database of clients, and I need to generate quarterly reports.
> I have a field called "Successful at Discharge", where "Dry" and "Cured" are
> two of the options. I also have a "Death" and "Other Complication" as open
> text fields.
>
> The report needs to contain (disaggregated by quarter):
> 1. # clients successful at discharge (either "dry" or "cured")
> 2. # clients with experiencing either "Death" or "Other Complication", as
> well as # clients with each. (although rare, a client could experience both)
> 3. The above figures divided over total records for a percentage
>
> I have the table called [VVF], then a query based on the table called
> [all query], and a report based on the query that dispays it nicely for
> printing.
>
> Ideally, I would like to be able to see a breakdown such as:
>
> Q1 #successful #death #other. #total complications #total clients
> Q2 #successful #death #other. #total compliations #total clients
> Q3 #successful #death #other. #total comp. #total
> clients
>
> somewhat like using the "count" function in pivot table, except in pivot
> table, I cannot isolate a count for the field "successful at discharge" that
> includes on "Dry" and "Cured".
>
> Please help!
>
> Alice
>
>
|
|
Thanks for the tip Al Camp! I'm wondering if it would be simplier to use
PivotView with the "query all", as only the stats are needed - not the actual
details of the records. (or by "report", did you mean the PivotTable view?)
That would also allow for disaggregation by quarter. Do you have any advice
on how to set that up to get the values? Sorry, a bit lost and not very good
with ACCESS. Thanks so much!
|
|
Ice,
First, a minor point... It's best to leave my comments in the thread of the discussion,
so I don't have to find my post and reread it to make sure I see the whole "train" of
thought in the problem. Just chain all your posts and mine. That way, someone else who
comes upon the thread can see the whole discussion in the latest post, and perhaps add
their suggestions too.
My suggestion was basically a "work around" for the fact that two pieces of information
were stored within the same field.
I haven't seen what data you have in each field, so I'm only discussing [Successful at
Discharge] here.
I think this is a good time to consider "breaking out" the Dry and Cured values into
two new fields... like SuccessDry and SuccessCured. This would be done by adding those 2
new fields to your table. and then running an Update query to copy the Dry and Cured
values from the [Successful at Discharge] field to their respective new fields. (avoid
spaces in field names)
Here's a sample Update criteria for the SuccessDry field...
IIF([Successful at Discharge] = "Dry", "Dry", "")
Now, your data is "normalized", and should pivot very easily.
You seemed to imply that your pivot table is OK right now... EXCEPT for Succesful at
Discharge... so this should take care of that, and... it's something that should be done
anyway... on general principles.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
There are 10 types of people in the world.
Those who understand binary, and those who don't.
"Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
news:CD21BFC8-B76D-4987-8FD4-98E0F6D0EEEF[ at ]microsoft.com...
[Quoted Text] > Thanks for the tip Al Camp! I'm wondering if it would be simplier to use
> PivotView with the "query all", as only the stats are needed - not the actual
> details of the records. (or by "report", did you mean the PivotTable view?)
> That would also allow for disaggregation by quarter. Do you have any advice
> on how to set that up to get the values? Sorry, a bit lost and not very good
> with ACCESS. Thanks so much!
|
|
Hello Al Camp:
Thanks so much for your advice, sorry to have truncated the thread.
Two questions, if you don't mind:
1. Still grappling with the thought of breaking up a field into two (or
more). Would you store it as numeric or text? For example, "1" for Dry and
"O" otherwise?
2. When is it appropriate to store data that way? If a field has 70 possible
options (like District of origin), would it make sense to keep it as one
field? I guess what I'm asking is, what is the utility of storing
information seperately? Can I still generate calculations with a single text
field?
3. WIth what expression could I add a field in this query to be able to
count the number of records with either "Death" or "Other Complication"
(stored in seperate fields, named as such). Some records may have both, but
I just need to know how many have either.
Thanks so much for your help, and for being patient with me!
Alice
"Al Camp" wrote:
[Quoted Text] > Ice, > First, a minor point... It's best to leave my comments in the thread of the discussion, > so I don't have to find my post and reread it to make sure I see the whole "train" of > thought in the problem. Just chain all your posts and mine. That way, someone else who > comes upon the thread can see the whole discussion in the latest post, and perhaps add > their suggestions too. > > My suggestion was basically a "work around" for the fact that two pieces of information > were stored within the same field. > I haven't seen what data you have in each field, so I'm only discussing [Successful at > Discharge] here. > > I think this is a good time to consider "breaking out" the Dry and Cured values into > two new fields... like SuccessDry and SuccessCured. This would be done by adding those 2 > new fields to your table. and then running an Update query to copy the Dry and Cured > values from the [Successful at Discharge] field to their respective new fields. (avoid > spaces in field names) > Here's a sample Update criteria for the SuccessDry field... > IIF([Successful at Discharge] = "Dry", "Dry", "") > > Now, your data is "normalized", and should pivot very easily. > You seemed to imply that your pivot table is OK right now... EXCEPT for Succesful at > Discharge... so this should take care of that, and... it's something that should be done > anyway... on general principles. > > -- > hth > Al Camp > Candia Computer Consulting - Candia NH > http://home.comcast.net/~cccsolutions> There are 10 types of people in the world. > Those who understand binary, and those who don't. > > > "Ice" <Ice[ at ]discussions.microsoft.com> wrote in message > news:CD21BFC8-B76D-4987-8FD4-98E0F6D0EEEF[ at ]microsoft.com... > > Thanks for the tip Al Camp! I'm wondering if it would be simplier to use > > PivotView with the "query all", as only the stats are needed - not the actual > > details of the records. (or by "report", did you mean the PivotTable view?) > > That would also allow for disaggregation by quarter. Do you have any advice > > on how to set that up to get the values? Sorry, a bit lost and not very good > > with ACCESS. Thanks so much! > > > >>Ice, > >>IBreak the Successful at Discharge values into two new fields called > >>Isomething Dry and
> >>IIn the query behind your pivot table, create two calculated columns. (remove the
> >>ISuccessful at Discharge field)
> >>IDry : IIF([Successful at Discharge] = "Dry", 1, 0)
> >>ICured : IIF([Successful at Discharge] = "Cured", 1, 0)
> >>INow use those two fields in your report.
> > >>IQ1 #Dry #Cured #death #other. #total complications #total clients
--
> >>Ihth
> >>IAl Camp
> >>ICandia Computer Consulting - Candia NH
> >>Ihttp://home.comcast.net/~cccsolutions
> >>IThere are 10 types of people in the world.
> >>IThose who understand binary, and those who don't.
"Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
news:3152BD0F-2DF3-43D3-9D3C-BB2A7F72C6AC[ at ]microsoft.com...
> Hello All:
>> >>
>> >> I have a database of clients, and I need to generate quarterly reports.
>> >> I have a field called "Successful at Discharge", where "Dry" and "Cured" are
> > >>two of the options. I also have a "Death" and "Other Complication" as open
> > >>text fields.
>
>> >> The report needs to contain (disaggregated by quarter):
>> >> 1. # clients successful at discharge (either "dry" or "cured")
> > >>2. # clients with experiencing either "Death" or "Other Complication", as
> > >>well as # clients with each. (although rare, a client could experience both)
>> >> 3. The above figures divided over total records for a percentage
>
> > >>I have the table called [VVF], then a query based on the table called
> > >>[all query], and a report based on the query that dispays it nicely for
>> >> printing.
>
> > >>Ideally, I would like to be able to see a breakdown such as:
>
>> >> Q1 #successful #death #other. #total complications #total clients
> > >>Q2 #successful #death #other. #total compliations #total clients
> > >>Q3 #successful #death #other. #total comp. #total
> > >>clients
>
>> >> somewhat like using the "count" function in pivot table, except in pivot
> > >>table, I cannot isolate a count for the field "successful at discharge" that
> > >>includes on "Dry" and "Cured".
>
> > >>Please help!
>
>> >> Alice
>
>
|
|
Ice,
Those questions are all dependent on the functionality of you application.
In order to answer those questions, I would really have to know all your data needs
(table design/s, data types, example values, possible future values, etc...), and also
have a good understanding of what output you'll need (forms, reports, queries, etc...) In
other words, your table design is entirely dependent on understanding the entire
application's functionality.
How tables are designed, and how those fields are designed is not a "one size fits all"
situation.
Your original question was... in a query "How do I count Dry and Cured entries within
the one field?", then my calculated fields in the query would be the way to go...
Q Dry Cured
Q1 16 21
Q2 11 6
Be aware that pivot tables and crosstab queies are not designed to total multiple
disparate field values. It's usually X against Y with results Z (Quarter vs Success with
Totals)
You're best bet to yield...
[Quoted Text] >>IQ1 #Dry #Cured #death #other. #total complications #total clients
as a one line display would be to use Pass Thru queries for those disparate fields, and
then combine them into one query (linked via Quarter)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
There are 10 types of people in the world.
Those who understand binary, and those who don't.
"Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
news:5A11AB87-AD9E-48ED-9E32-972A4C723507[ at ]microsoft.com...
> Hello Al Camp:
>
> Thanks so much for your advice, sorry to have truncated the thread.
>
> Two questions, if you don't mind:
>
> 1. Still grappling with the thought of breaking up a field into two (or
> more). Would you store it as numeric or text? For example, "1" for Dry and
> "O" otherwise?
>
> 2. When is it appropriate to store data that way? If a field has 70 possible
> options (like District of origin), would it make sense to keep it as one
> field? I guess what I'm asking is, what is the utility of storing
> information seperately? Can I still generate calculations with a single text
> field?
>
> 3. WIth what expression could I add a field in this query to be able to
> count the number of records with either "Death" or "Other Complication"
> (stored in seperate fields, named as such). Some records may have both, but
> I just need to know how many have either.
>
> Thanks so much for your help, and for being patient with me!
>
> Alice
>
> "Al Camp" wrote:
>
>> Ice,
>> First, a minor point... It's best to leave my comments in the thread of the
>> discussion,
>> so I don't have to find my post and reread it to make sure I see the whole "train" of
>> thought in the problem. Just chain all your posts and mine. That way, someone else
>> who
>> comes upon the thread can see the whole discussion in the latest post, and perhaps add
>> their suggestions too.
>>
>> My suggestion was basically a "work around" for the fact that two pieces of
>> information
>> were stored within the same field.
>> I haven't seen what data you have in each field, so I'm only discussing [Successful
>> at
>> Discharge] here.
>>
>> I think this is a good time to consider "breaking out" the Dry and Cured values into
>> two new fields... like SuccessDry and SuccessCured. This would be done by adding those
>> 2
>> new fields to your table. and then running an Update query to copy the Dry and Cured
>> values from the [Successful at Discharge] field to their respective new fields. (avoid
>> spaces in field names)
>> Here's a sample Update criteria for the SuccessDry field...
>> IIF([Successful at Discharge] = "Dry", "Dry", "")
>>
>> Now, your data is "normalized", and should pivot very easily.
>> You seemed to imply that your pivot table is OK right now... EXCEPT for Succesful at
>> Discharge... so this should take care of that, and... it's something that should be
>> done
>> anyway... on general principles.
>>
>> --
>> hth
>> Al Camp
>> Candia Computer Consulting - Candia NH
>> http://home.comcast.net/~cccsolutions
>> There are 10 types of people in the world.
>> Those who understand binary, and those who don't.
>>
>>
>> "Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
>> news:CD21BFC8-B76D-4987-8FD4-98E0F6D0EEEF[ at ]microsoft.com...
>> > Thanks for the tip Al Camp! I'm wondering if it would be simplier to use
>> > PivotView with the "query all", as only the stats are needed - not the actual
>> > details of the records. (or by "report", did you mean the PivotTable view?)
>> > That would also allow for disaggregation by quarter. Do you have any advice
>> > on how to set that up to get the values? Sorry, a bit lost and not very good
>> > with ACCESS. Thanks so much!
>>
>>
>> >>Ice,
>> >>IBreak the Successful at Discharge values into two new fields called > >>Isomething
>> >>Dry and
> Cured.
>> >>IIn the query behind your pivot table, create two calculated columns. (remove the
>> >>ISuccessful at Discharge field)
>> >>IDry : IIF([Successful at Discharge] = "Dry", 1, 0)
>> >>ICured : IIF([Successful at Discharge] = "Cured", 1, 0)
>> >>INow use those two fields in your report.
>
>> > >>IQ1 #Dry #Cured #death #other. #total complications #total clients
>
> --
>> >>Ihth
>> >>IAl Camp
>> >>ICandia Computer Consulting - Candia NH
>> >>Ihttp://home.comcast.net/~cccsolutions
>> >>IThere are 10 types of people in the world.
>> >>IThose who understand binary, and those who don't.
>
>
> "Ice" <Ice[ at ]discussions.microsoft.com> wrote in message
> news:3152BD0F-2DF3-43D3-9D3C-BB2A7F72C6AC[ at ]microsoft.com...
>> Hello All:
>>> >>
>>> >> I have a database of clients, and I need to generate quarterly reports.
>>> >> I have a field called "Successful at Discharge", where "Dry" and "Cured" are
>> > >>two of the options. I also have a "Death" and "Other Complication" as open
>> > >>text fields.
>>
>>> >> The report needs to contain (disaggregated by quarter):
>>> >> 1. # clients successful at discharge (either "dry" or "cured")
>> > >>2. # clients with experiencing either "Death" or "Other Complication", as
>> > >>well as # clients with each. (although rare, a client could experience both)
>>> >> 3. The above figures divided over total records for a percentage
>>
>> > >>I have the table called [VVF], then a query based on the table called
>> > >>[all query], and a report based on the query that dispays it nicely for
>>> >> printing.
>>
>> > >>Ideally, I would like to be able to see a breakdown such as:
>>
>>> >> Q1 #successful #death #other. #total complications #total clients
>> > >>Q2 #successful #death #other. #total compliations #total clients
>> > >>Q3 #successful #death #other. #total comp. #total
>> > >>clients
>>
>>> >> somewhat like using the "count" function in pivot table, except in pivot
>> > >>table, I cannot isolate a count for the field "successful at discharge" that
>> > >>includes on "Dry" and "Cured".
>>
>> > >>Please help!
>>
>>> >> Alice
>>
>>
|
|
|