Chart reports 1

[LOSERMANN]

good day all,

im trying to create a report that will display some stats. to begin with i have a qry that contains client information for a good many clients. here is what ive got so far. here is what im aiming for;

percentage of clients that obtain employment in last 60 days.

percentage of clients that show an earning progression in the last 60 days.

percentage of client cases that closed with earnings.

i have been able to 'count' the total clients in another qry. this works nicely but i need to go a bit beyond this.

so i created another qry that counts clients that have obtained employment in the last 60 days. this gives me a nice number and groups it by date alowing me to chart it.

the major problem im having is that i cant get the chart to show the percentages, all i can get is those counts.

i hope i have outlined my troubles adequately and look forward to any advise that can be offered.

regards,

Milk

 

[LOSERMANN]

to expand on my earlier posting;

my source date looks something like this...

qryClients

(the Fields)
ClientID
ReferalDate (date refered to services)
EarningsStart (starting salary)
EarningsEnd (salary at end of case)
CaseClosedDate (date case closed)

 

[dhookom]

You should really take the time to enter some sample records and then the expected display. You are asking us to make too many assumptions regarding your calculations.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

Thanks for your quick reply.

i hope this helps.

in the end im looking for 3 bar charts

-percentage of clients that obtained employment in last 60 days

-percentage of clients that show an earning progression

-percentage of clients cases closed with earnings

thusfar i have been able to;

count the total cases by isolating the id# in a qry and having the qry count the records.
SELECT Count(qryClients.[ID#]) AS TotalClients
FROM qryClients;
SELECT Count(qryClients.[ID#]) AS [CountOfID#], qryClients.[Referal Date]

count cases that showed earnings when their cases closed.
FROM qryClients
WHERE (((qryClients.CaseClosedDate) Like "*") AND ((qryClients.EarningsEnd) Like "*"))
GROUP BY qryClients.[ReferalDate];

count the records that showed an earning progression.
SELECT Count(qryClients.[ID#]) AS [CountOfID#], qryClients.[ReferalDate]
FROM qryClients
WHERE (((qryClients.EarningsEnd)>[qryClients]![EarningsStart]))
GROUP BY qryClients.[ReferalDate];

count the records that shows the number of clients that found employment within 60 days of referal.
SELECT Count(qryClients.[ID#]) AS [CountOfID#], qryClients.[ReferalDate]
FROM qryClients
WHERE (((qryClients.EmploymentStart) Between Now() And Now()-60))
GROUP BY qryClients.[Referal Date];

all of these give me what i would call good numbers. problem is i need to generate a bar chart for each and express on the chart percentages rather than absolute numbers (like 4 people found employment in june)

i hope this helps explain my troubles.

regards,

Milk

 

[dhookom]

It sounds like your bar charts have only one bar. In general, the Chart control will use its Row Source query to display a graph representing the data.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

yes one bar but as the months go by im hoping to have one bar for each month. thusfar ive not been able to compute the percentage and have it displayed in the chart.


regards,

milk

 

[dhookom]

Let us know when you are finished adding requirements (ie one bar per month).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

im finished. thanks again for taking the time to look into this for me.


regards,

milk

 

[dhookom]

You need to be able to create your query with your values prior to assuming that you can graph them. I can't understand from your queries what you use to count total clients and which found employment from referrals.
Maybe you should type in about 12 records and then explain to us how you would calculate your various totals for the graph.




Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

got it working. ive been tinkering with it some more and here is what ive gotten to work.

i created three qrys, one that counts the total client records. another that counts the records that meet a certain criteria. in a third qry i have combined these two counts and built an expression that divides the two counted fields and produces a decimal.

qryStatsClientCount
SELECT Count(qryClients.[ID#]) AS [TotalClients]
FROM qryClients;

qryStatsObtEmploy60
SELECT Count(qryClients.EarningsStart) AS [ObtainedEmployment60Days]
FROM qryClients;

qryCombined
SELECT qryStatsClientCount.[TotalClients], qryStatsObtEmploy60.[Obtained Employment 60 Days], [ObtainedEmployment60Days]/[TotalClients] AS Expr1
FROM qryStatsClientCount, qryCombined;

this gives me the number i need. then...

in a form i created chart object and linked it ti qryCombined bringing over [expr1] and [ObtainedEmployment 60Days]. formatted the axis to percentage and wala! a nice little one column chart that fits the bill.

to me this seems a bit bulky and perhaps a back A#$ward way of doing it. in its defense though i have to say it works.

thanks for you assistance, i would welcome any comments/observations/advise on this.

Warmest regards,

Milk

 

[LOSERMANN]

oops, i just realized i left something out. im still working on getting the little chart thing to display this data over a period of months as they go by, adding bars as time passes. as it is now i get a 'snapshot' view of the stats.

regards,

milk

 

[dhookom]

First of all, do you have a query that returns the number of active clients during each month? I believe this would be for June of 2004, all of those clients with a ReferralDate >= 6/1/2004 and CaseClosedDate <=6/30/2004.

Is this correct?

You can't compare numbers unless you have a method to calculate them.

Do you have an issue with providing about 12 records for us to see what your data is and then how you would expect to calculate the results?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

unfortunately i cant share the records. i do have fields for ReferalDate and CaseCLosedDate, though i havent done a qry to compare them.

regards,

milk

 

[dhookom]

You could type in (make up) about 12 records. They only have to accurately describe your actual data.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

i tried to put together a tbl of records, but when i previewed it it came out all disjointed. i tried to do a screen capture and then crop it to paste it in here and failed...short of that here is something that may or may not help.

tblClients
fldID# - Primary Key
ReferalDate - Date/Time
EmploymentStart - Date/Time
EmploymentEnd - Date/Time
EarningsStart - Number
EarningEnd - Number
CaseClosedDate - Date/Time

all clients have an ID# and a referal date. those that have found employment have a date entered in [EmploymentStart]on that date and a blank in the [EmploymentEnd] fld. all those that have employment will also have a number in the [EarningStart] fld. those who are working and then loose thier job will have everything filled in except [CaseClosedDate]. cases can close with or without a job (most close with a job).

i cant thank you enough for sticking with me on this.

Milk

 

[dhookom]

It would probably take me about 20 minutes to type in 12 fake records and then figure out how I would expect them to look in a report. If you can't spare the time to do this then I find it difficult to spare the time to guess at what you have and what you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

Duane, ive tried to type them into this forum but when I preview the message it looks very disjointed and mixed up, ive tried to do a screen capture and then paste that, didn’t work. I so much appreciate the time you have given me, it’s much more than one can reasonably expect. Im not sure what else to do besides site this portion of the project aside for the time being and then revisit it at a later point. Id like to give you a dozen stars for your tenacious hard work in trying to help me along, thank you.

 

[LOSERMANN]

heres a thought!!

i didnt know poo poo about crosstab qrys but have managed to create one that gives a glimmer of hope.

TRANSFORM Count(qryClients.[ID#]) AS [CountOfID#]
SELECT qryClients.EmploymentStart, Count(qryClients.[ID#]) AS [Total Of ID#]
FROM qryClients, qryStatsClientCount
GROUP BY qryClients.EmploymentStart
PIVOT Format([Referal Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

this looks to be a promising start in the right direction...the only thing missing is to be able to come up with a percentage for each month. the total client by month number is great. i just need to get that percentage.

any ideas? for now i think im on the right track and will keep tinkering with it and post my finding just in case there is another amature of my low caliber out there.

regards,

milk

 

[dhookom]

You can use [ignore][tt][/ignore]your text and [ignore][/tt][/ignore] to get a fixed space font in a message. For instance
[tt]
1234 67 90123
This is fixed
Find an eagle
1234 67 90123
[/tt]
Click on the Preview Post to see the results prior to posting.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LOSERMANN]

would it be possible if i could email a copy of the dbase to you?

regards,

Milk