Help Creating A Chart - Counting Nulls

[BerkshireGuy]

I have the following dataset:

PolNum DateInterviewComplete
---------------------------------------------
1234 11/23/2006
1323 11/03/2006
4342 Null
2322 Null

I want to create a chart that shows the percentage completed -
DateInterviewComplete has a value and percent In Progress -
DateInterviewComplete IsNull.

So the above example would be 50% Complete - 50% In Progress.

Any ideas?

Thanks!

[kingston via AccessMonster.com]

Try this:

Complete = DCount("*","Table","[DateInterviewComplete] Is Not Null")
In Progress = DCount("*","Table","[DateInterviewComplete] Is Null")

Divide by the total number of records and multiply by 100 to get a percentage.
BerkshireGuy wrote:

>I have the following dataset:

PolNum DateInterviewComplete
---------------------------------------------
1234 11/23/2006
1323 11/03/2006
4342 Null
2322 Null

I want to create a chart that shows the percentage completed -
DateInterviewComplete has a value and percent In Progress -
DateInterviewComplete IsNull.

So the above example would be 50% Complete - 50% In Progress.

Any ideas?

Thanks!

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

BerkshireGuy wrote:

PolNum DateInterviewComplete
---------------------------------------------
1234 11/23/2006
1323 11/03/2006
4342 Null
2322 Null

I want to create a chart that shows the percentage completed -
DateInterviewComplete has a value and percent In Progress -
DateInterviewComplete IsNull.

So the above example would be 50% Complete - 50% In Progress.

OK, so combine all the results and anything with a date in
DateInterviewComplete would be "Complete" and anything without a date
(null) would be "In Progress".

Create a calculated field in the SQL/query that your graph is based upon
and use the iif() function. In the query design grid, the field would
look like this (I called it status) - wathc for wrap, this is a single line:

Status: iif(isnull(DateInterviewComplete), "In Progress", "Complete")

That should do it for you. I'd want to do a chart whereby the series is
Status. That way, you can show the in progress versus completed using a
stacked, or even a 100% stacked, column or bar chart.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[BerkshireGuy]

Thank you both for the suggestions.

Any good reading material to demonstrate what the best chart views
should be used in different situations?

Thanks.
Brian

Tim Marshall wrote:

BerkshireGuy wrote:

PolNum DateInterviewComplete
---------------------------------------------
1234 11/23/2006
1323 11/03/2006
4342 Null
2322 Null

I want to create a chart that shows the percentage completed -
DateInterviewComplete has a value and percent In Progress -
DateInterviewComplete IsNull.

So the above example would be 50% Complete - 50% In Progress.

OK, so combine all the results and anything with a date in
DateInterviewComplete would be "Complete" and anything without a date
(null) would be "In Progress".

Create a calculated field in the SQL/query that your graph is based upon
and use the iif() function. In the query design grid, the field would
look like this (I called it status) - wathc for wrap, this is a single line:

Status: iif(isnull(DateInterviewComplete), "In Progress", "Complete")

That should do it for you. I'd want to do a chart whereby the series is
Status. That way, you can show the in progress versus completed using a
stacked, or even a 100% stacked, column or bar chart.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

BerkshireGuy wrote:

Any good reading material to demonstrate what the best chart views
should be used in different situations?

I've no idea. If you find something on line, let us know, I'm sure I'm
regularly offending the graph gods!!! Seriously, there are intuitive
(to me) guidelines I sort of follow, but anything formal I'm sadly
lacking. My main app that uses graphs is fully automated, so I
alternate between various chart types with the click of a toggle in an
option group until I find something that easily conveys what I want to
portray. I've always meant to look at help on charts in PowerPoint as
that might provide some practical guidance.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me