Help with Graph

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a select query that shows the following results:

Date Rejects
01-03 1
01-04 10
01-05 9
01-06 7
01-10 24

But when I chart this in Access for the dates 1/7 - 1/9 it doesn't drop down
to the 0 line it just shows no points.
Is there something in the query or code I can write to first fill in the
dates that are missing and add a 0 if there is no results?

Thanks!
Stacey
 
An easy way of handling this is to create a table that has all the dates you
want to use. In the query you want to add the table with all dates (table1)
and the table that has your dates with data (table2).

Create a join in a query by moving the table1 dates field on to table2 date
field. Modify the join by right clicking and chosing the 2nd option

Then when you create your criteria it will show up as null. Your chart will
interpert this as a zero.
 
Did that and still didn't work, even tried option #3, still didn't work.
Here is my SQL statement to see if I am missing something:
SELECT Format([Occurrence_Date],"mm-dd") AS [Occur Date],
Sum(qry_Sum_for_cht_NPLC_Rejects_by_Day.Qty_of_Defect) AS SumOfQty_of_Defect,
tbl_Date.Date
FROM tbl_Date LEFT JOIN qry_Sum_for_cht_NPLC_Rejects_by_Day ON tbl_Date.Date
= qry_Sum_for_cht_NPLC_Rejects_by_Day.Occurrence_Date
WHERE (((qry_Sum_for_cht_NPLC_Rejects_by_Day.[Todays Month])=[Occur Month]))
GROUP BY Format([Occurrence_Date],"mm-dd"), tbl_Date.Date
ORDER BY Format([Occurrence_Date],"mm-dd");

Thanks!!
 
Back
Top