MS ACCESS Graph with fixed X values

[johny6685]

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

eg:-

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

PARAMETERS [Forms]![frmDBC]![txtProdDt] DateTime;
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]
FROM tblProdctnDtl
WHERE (((tblProdctnDtl.ProductionDt)=DateAdd("d",-1,Date())) AND ((tblProdctnDtl.ProcessorID) Like "JDAS"))
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)
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

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.

[johny6685]

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?

[johny6685]

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.

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)

[johny6685]

I have even tried this, but I am still unlucky

In ( (DateAdd("h",1,([Forms]![frmDBC]![lblogtim])))

[Rabbit]

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.