 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006quarterly report help!
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. > Q1 #Dry #Cured #death #other. #total complications #total clients  Signaturehth 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. > Hello All: > [quoted text clipped - 27 lines] > > 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. Signaturehth 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. > 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 > Ice, > First, a minor point... It's best to leave my comments in the thread of the discussion, [quoted text clipped - 30 lines] > >>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. > Hello All: >> >> [quoted text clipped - 27 lines] > >> >> 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... >>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) Signaturehth 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. > Hello Al Camp: > [quoted text clipped - 109 lines] >> >>> >> Alice |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.