Solved Issues with Pie Chart

[nashaz]

Hi all

First time using the modern charts in access. I am trying to plot a simple pie chart which depicts how many of courses are still in date and how many are out of date. My query (lets call it qry1) has CertificationDate, ExpiresOn, DaysLeft fields. However, I cannot get it to plot on a pie! I tried making a new query (qry2) which has these 3 fields:

1. Count(*) - to count all records
2. CountValid: Count(IIf([ExpiresOn] > Date(), 1, Null))
3. CountInvalid: Count(IIf([ExpiresOn] < Date(), 1, Null))

I thought it would be simple to use qry2 to get the chart to show what I want but it just doesn't work! I think it could be down to my Transformed Row Source being read-only.

Appreciate any and every suggestion!

 

[theDBguy]

Are you able to share a sample db?

 

[nashaz]

Are you able to share a sample db?

Unfortunately, cant share the db as I will have to cut down a lot. But I was able to get around the issue by creating a new query with the following SQL:

SELECT 'Valid' AS Status,
    SUM(IIf([DaysLeft] > 0, 1, 0)) AS Count
FROM AllDeliveredQ

UNION ALL

SELECT 'Invalid' AS Status,
    SUM(IIf([DaysLeft] < 0, 1, 0)) AS Count
FROM AllDeliveredQ;

Seems to be working fine!

 

[cheekybuddha]

Seems to be working fine!

What happens if DaysLeft = 0 ?

I guess you could also use:

SELECT
  t.Status,
  COUNT(*) AS [Count]
FROM (
  SELECT
    IIf(Days left >= 0, 'Valid', 'Invalid') AS Status
  FROM AllDeliveredQ
) t
GROUP BY
  t.Status
;

 

[nashaz]

What happens if DaysLeft = 0 ?

I guess you could also use:

SELECT
  t.Status,
  COUNT(*) AS [Count]
FROM (
  SELECT
    IIf(Days left >= 0, 'Valid', 'Invalid') AS Status
  FROM AllDeliveredQ
) t
GROUP BY
  t.Status
;

good catch, lol! Thank you. I will made the suggested change for DaysLeft=0