Login    Forum    Register    Search    FAQ

Board index » HELP AND ADVICE » GAME/SOFTWARE/HARDWARE PROBLEMS

It is currently Wed Nov 27, 2024 5:11 am
All times are UTC [ DST ]
View unanswered posts | View active topics
 



Post new topic Reply to topic  Page 1 of 1
  [ 6 posts ] 

Access charts

Author Message
Caspius=GCHQ=
 Post subject: Access charts
 Post Posted: Fri Oct 12, 2007 11:49 am 
Offline
The Groundkeeper
User avatar

Joined: Fri Mar 26, 2004 7:59 pm
Posts: 6056
Location: UK
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?

_________________
Image
Top 
 Profile  
 
Caspius=GCHQ=
 Post subject:
 Post Posted: Sun Oct 14, 2007 12:16 pm 
Offline
The Groundkeeper
User avatar

Joined: Fri Mar 26, 2004 7:59 pm
Posts: 6056
Location: UK
Soo we got no Access users on here then? :? :D

_________________
Image
Top 
 Profile  
 
Arma
 Post subject: Re: Access charts
 Post Posted: Sun Oct 14, 2007 12:31 pm 
Offline
PFY wannabe
User avatar

Joined: Sun Jan 02, 2005 11:51 pm
Posts: 3295
Location: South, but not far south enough :/
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 :)

_________________
Image

"I wish my lawn was emo, so it would cut itself...."
Top 
 Profile  
 
Caspius=GCHQ=
 Post subject: Re: Access charts
 Post Posted: Sun Oct 14, 2007 12:38 pm 
Offline
The Groundkeeper
User avatar

Joined: Fri Mar 26, 2004 7:59 pm
Posts: 6056
Location: UK
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.

_________________
Image
Top 
 Profile  
 
Caspius=GCHQ=
 Post subject:
 Post Posted: Sun Oct 14, 2007 1:06 pm 
Offline
The Groundkeeper
User avatar

Joined: Fri Mar 26, 2004 7:59 pm
Posts: 6056
Location: UK
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));

_________________
Image
Top 
 Profile  
 
Caspius=GCHQ=
 Post subject:
 Post Posted: Sat Dec 01, 2007 6:04 pm 
Offline
The Groundkeeper
User avatar

Joined: Fri Mar 26, 2004 7:59 pm
Posts: 6056
Location: UK
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

_________________
Image
Top 
 Profile  
 
Display posts from previous:  Sort by  
 
Post new topic Reply to topic  Page 1 of 1
  [ 6 posts ] 

Board index » HELP AND ADVICE » GAME/SOFTWARE/HARDWARE PROBLEMS

It is currently Wed Nov 27, 2024 5:11 am
All times are UTC [ DST ]
View unanswered posts | View active topics
 

Who is online

Users browsing this forum: No registered users and 8 guests

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
  • Shoutbox

  • Shout Message


test