By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

MS ACCESS Graph with fixed X values

P: 66
Hi Team,

I would like to show the productivity in a bar graph in a form, I created pivot chart and inserted but data labels are fails to show and major disadvantage is it is not working in 2013 as the pivot chart was removed. Then I created a cross tab and added a Graph in form but that has a problem in showing the X axis values as the cross tab query will show the data in column order.

Now I tried with select query in row order and tried to run the graph but it is not showing me all working hours.

For eg. if we create query to show data column wise we can add IN statement and run the query but I would like to show column wise here is there any possiblity to show 9 hours mandatoryly and show the relevant count if exists


Hours | Count
18 3
19 5
20 0
21 2
22 2
23 1
24 0
01 6
02 7
03 8

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmDBC]![txtProdDt] DateTime;
  2. SELECT Format([PrcsEnTime],"hh") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) AS Hours, Count(tblProdctnDtl.ProcessorID) AS [Count]
  3. FROM tblProdctnDtl
  4. WHERE (((tblProdctnDtl.ProductionDt)=DateAdd("d",-1,Date())) AND ((tblProdctnDtl.ProcessorID) Like "JDAS"))
  5. GROUP BY Format([PrcsEnTime],"hh") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)
  6. HAVING (((Format([PrcsEnTime],"hh") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24))>18));
I tried the above in select query but 0 results, but When I dont add
Expand|Select|Wrap|Line Numbers
  1. IN  (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24))>18));
the query gives output but only the worked hours.

I want to display all hours and the values for worked hours.

Please help me whether I can plot the graph in any other ways.
Feb 6 '15 #1
Share this Question
Share on Google+
4 Replies

P: 66
OMG!!!! :-O I just fixed it just by clicking on BY ROW in the Graph edit window by double clicking it..

Now I have a challenge to restrict the 24 hours to 9 hours from the login time...

But I am not sure how to make changes in "IN (1,2,3,4...,24)" statement ....

will it accept formulas?
Feb 6 '15 #2

P: 66
BTW it is not accepting the code within the IN statement, any one please help me out on how to make the hours restricted for each processor login time.

Expand|Select|Wrap|Line Numbers
  1. In ( (Format(([Forms].[frmDBC].[lblogtim]),'hh'))+1,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+2,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+3,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+4,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+5,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+6,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+7,(Format(([Forms].[frmDBC].[lblogtim]),'hh'))+8)
Feb 6 '15 #3

P: 66
I have even tried this, but I am still unlucky

Expand|Select|Wrap|Line Numbers
  1. In ( (DateAdd("h",1,([Forms]![frmDBC]![lblogtim])))
Feb 6 '15 #4

Expert Mod 10K+
P: 12,430
You will need to outer join the results to a table that contains a record for each hour so that you can insert blank rows for those hours that have no data.
Feb 6 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.