Chart with zero values

[filterjon]

Hello all,

I am trying to create a chart in access using a query as the data source. A sample of the data is

Lead Time Value
1 200
2 300
3 400
5 200

I would like to display a chart that shows the data including a leadtime of 4 with value 0 even though there is no leadtime of 4 in my query results.

Any suggestions?

Thanks
Jon

 

[Crowley16]

get the query to bring back a 0 when lead time is 4...

either with the use of left joins, subquerys and the nz function...

--------------------
Procrastinate Now!

 

[filterjon]

Thanks for the reply... but,

In this case, there is no leadtime of 4. In effect I am trying to show the leadtime data over an equi-distant scale and not squash the data together.

If I do a scattergraph the data looks right, i.e. the bottom axis has equal time divisions on it. However, this graph is very difficult to read.

Any other suggestions?

 

[Crowley16]

in that case, generate a virtual lead scale that's only loosely based on lead times...

and then link your lead times to this new scale...

the easiest way would be to create a temp table with an autonumber, have the autonumber start at your min lead time (or 0 if you want your graph to start at 0), and then fill the table with random data till the autonumber reaches your max lead time...

or something along those lines...

--------------------
Procrastinate Now!

 

[MajP]

If you know how many lead times you could go up to you could make a required values table.

tblRequiredValue
intRequiredValue

in this case it just has values 1,2,3,4,5, 6 ....to some big value. Now do everything Crowley said using a left join and NZ function.

SELECT tblRequiredValues.tblRequiredValues, NZ([intLeadTimeValue],0) AS intLeadTimeValueNZ
FROM tblRequiredValues LEFT JOIN tblLeadTime ON tblRequiredValues.tblRequiredValues=tblLeadTime.intLeadTime
WHERE (((tblRequiredValues.tblRequiredValues)<=DMax("intLeadTime","tblLeadTime")));

This returns the data in the format you said with value for lead times of 1,2,3,4,5

 

[MajP]

Made a mistake. The field name in the query should read "intRequiredValue" not "tblRequiredValue"

SELECT tblRequiredValues.[intRequiredValue], NZ([intLeadTimeValue],0) AS intLeadTimeValueNZ
FROM tblRequiredValues LEFT JOIN tblLeadTime ON tblRequiredValues.[intRequiredValue]=tblLeadTime.intLeadTime
WHERE (((tblRequiredValues.[intRequiredValue])<=DMax("intLeadTime","tblLeadTime")));

 

[MajP]

Another way would be to use recordsets and add in empty values. If you used your query to make a temp table, you could find the largest value in the table and then count from 1 to the largest value adding a lead time and a lead time value for missing lead times.

Public Sub addEmptyLeadTimes()
  Dim rs As DAO.Recordset
  Dim intMaxLead As Integer
  Dim intCounter As Integer
  Set rs = CurrentDb.OpenRecordset("SELECT * from tmpTblLeadTime ORDER BY intLeadTime")
  rs.MoveLast
  intMaxLead = rs.Fields("intLeadTime")
  rs.MoveFirst
  For intCounter = 1 To intMaxLead
    rs.FindFirst ("intLeadTime = " & intCounter)
    If rs.NoMatch Then
      rs.AddNew
      rs.Fields("intLeadTime") = intCounter
      rs.Fields("intLeadTimeValue") = 0
      rs.Update
    End If
  Next intCounter
End Sub

The preferred way would be like Crowley said using sql.

 

[filterjon]

Thanks for your replies. I'll start coding and let you know how it goes.

Cheers
Jon