Gamecommunity =GCHQ=
http://forum.gamecommunity.co.uk:8080/

Access charts
http://forum.gamecommunity.co.uk:8080/viewtopic.php?f=6&t=46466		 Page 1 of 1
Author:  Caspius=GCHQ= [ Fri Oct 12, 2007 11:49 am ]
Post subject:  Access charts
This is the formulae use to create the chart.

Code:
SELECT [1-1],Count(*) AS [Count] FROM [MainRecords]   GROUP BY [1-1];


The field has a drop box that contains Yes, No and Don't know. The problem is the pie chart also picks up the fields that have no answer in them. leading to four slices in the Pie Chart, not three. Is there anyway to make Access ignore empty fields?
Author:  Caspius=GCHQ= [ Sun Oct 14, 2007 12:16 pm ]
Post subject: 
Soo we got no Access users on here then? :? :D
Author:  Arma [ Sun Oct 14, 2007 12:31 pm ]
Post subject:  Re: Access charts
Try

Code:
SELECT [1-1],Count(*) AS [Count] FROM [MainRecords] WHERE ((Count.[Your_testing_field_here]) Is Not Null)  GROUP BY [1-1];


Adapted from : http://office.microsoft.com/en-us/acces ... 21033.aspx

I dont know access but I do know a little about SQL :)
Author:  Caspius=GCHQ= [ Sun Oct 14, 2007 12:38 pm ]
Post subject:  Re: Access charts
Arma=GCHQ= wrote:
Try

Code:
SELECT [1-1],Count(*) AS [Count] FROM [MainRecords] WHERE ((Count.[Your_testing_field_here]) Is Not Null)  GROUP BY [1-1];


Adapted from : http://office.microsoft.com/en-us/acces ... 21033.aspx

I dont know access but I do know a little about SQL :)


Thanks, thats just what I wanted 8) 8) I'll give it a whirl. Knew it was somthing to do with a null value, just couldn't work out how it fitted into the formulae.
Author:  Caspius=GCHQ= [ Sun Oct 14, 2007 1:06 pm ]
Post subject: 
That one didn't work, minor tweak later and it did though. :D :D

Code:
SELECT MainRecords.[1-1], Count(*) AS [Count] FROM MainRecords GROUP BY MainRecords.[1-1] HAVING (((MainRecords.[1-1]) Is Not Null));
Author:  Caspius=GCHQ= [ Sat Dec 01, 2007 6:04 pm ]
Post subject: 
More Access...

I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.

It all works fine but the chart that is based on the query only shows months that have an entry.

Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows.

[TextPriDate] is the start date
[TextPriDate2] is the end date

This is the query code (QryDate)
Code:
SELECT
   tblMain.ID1, tblMain.Dt
FROM
   tblMain
WHERE
   (((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));


This the code from the chart in the Report
Code:
SELECT
   (Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count]
FROM
   QryDate
GROUP BY
   (Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);


I've tried a few variations on the coding but I suppose working with null values is like trying to create something out of nothing.

Theres 18 records July which shows fine but the other months there are currently no records, but I want to show them as zero on the chart.

Image

Casp
Page 1 of 1 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/