Turning True/False/N-A into percentages for access chart

[g4tv4life]

Hey guys,
I am trying to use built in access charting create a simple line graph but I need help formating the data the way I need it.
Right now I have a query that boils down the information into 12 records, one for each month of the year as a yes/no/N-a
format (1 for true, 0 for false, 2 for NA). Now I want to chart the line as a simple average for the year (I.E. January is a yes,


100% then February is a no, drops down to 50%, etc). Lastly, I want it to just use last months average if its a N/A and not
have that month factor into the average.

Any idea's how I can possibly do this?

[June7]

I suppose you will need to do calculation in query to convert the Yes/No/NA.

What do you mean by 'average for the year' - don't you want to plot 12 months?

Nulls are ignored in aggregate calcs. What do you mean by 'use last months average' if its a N/A? Why would you want to carry one month's data to another month?

[g4tv4life]

by average for the year, I meant just a average OVER the year.

As for use last months average, I meant I didn't want the N/A (which can be a null if it makes it easier) to affect the average.

I'm not sure how exactly I can calculate a percent average over the year by a calculated field as I've never worked with them,
Could you suggest how I could go about that?

[drexasaurus]

Like June said, Nulls are ignored, so if you don't want the value being counted or summed to affect the value, make sure the NAs are converted to null. If you did, you would have to convert them to zeros or some other representative value. If you are just getting the averages of the numbers 1 and 0 then you would use an aggregate function over that column. If you are using a report you could also use a function at the end--but you say you are using a built-in chart. I am better at this with T-SQL than with access SQL, but I know you can also do something like AVG(Iif([SomeValue]=2, Null, [SomeValue]) -- replace Null with 0 if you did want it to count.

It also seems you might be looking for a running average? Like the entire average up to that point each month? That's more complicated, but if so you would have to create some complicated report that produced a running average for each month column. I'm not even going to think on dwelling on that one unless I know that's what you need and have a tested solution.

[June7]

How do you get the query that generates the 0/1/2 values? Why not 50/100/Null instead?

Otherwise, maybe like:

SELECT *, Choose([fieldname], 50, 100, Null) AS Percentage FROM table or query name;

Then use that query as source for subsequent queries.

[g4tv4life]

Let me breakdown my entire system as it stands so far. I have 80 facilities, each with either a yes/no or N/A(which I've changed to now be null)for each month.
These values are put into a table with both the date of the test, the facility and the response. The query breaks it down to a year range and facility based on
what facility is selected in a form.
drexasaurus, you are correct I need a running average, that's the term that was escaping me and where the complexity of this sits.
Also, sadly this has to be a form because some minor edits in the data will be made.

[June7]

Running sum in query and form not easy. Review
http://support.microsoft.com/kb/205183
http://www.blueclaw-db.com/download/..._sum_query.htm
or
domain aggregate (DSum)
http://rogersaccessblog.blogspot.com...nning-sum.html
http://support.microsoft.com/kb/290136

[g4tv4life]

I spent a few hours toying with the code and realized it just wasn't in the cards. I've swappedto doing it through access automation through excel charting.