Time line Graph report

[witharebelyell]

Hi

I have a simple question..how do i do a time line graph..

I have a database that record 'defects', each defect has a start and
end date.
I want to plot a count of how many 'open' defects on any particular
day. Sound easy?

I want the x-axis, to display each day on the reporting period. The y-
axis for that day says how many were open.

How do i plot this so that on the x-axis i have 'each' day within the
reporting period

I would probably like to use a line graph.

mike

 

[Guest]

I would first create a table of all dates
tblDates
=============
TheDate

Then create a query of tblDates with your defects table. Don't join the
tables. Set the criteria under TheDate to something like
Between [Start] and [End]
Group this query the TheDate and count the defects.

Use this query as the Row Source of your chart.

 

[witharebelyell]

Thanks Duane

So you suggest a cartesian product? (named after de cartes ? - its
amazing what you can discover sick in bed looking at the ceiling, i
digress..) , Yes, thats the only way i could think of too (no join)!

I was hoping there was some easier way. I think the user would like to
specify the reporting period but - so the problem i have i would have
to create the table of dates on the fly. (probably by some other
catesian product). Whats the easiest was to create this table of
dates, seeing i don't know what reporting period they might sepcify?.

thanks
mike

 

[Guest]

A table of all dates should take about 5 minutes using Access and Excel. Open
Excel and create a column of dates from the earliest you will ever need to
the maximum of what you will ever need. Copy this column and paste it into
tblDates.TheDate.

The Cartesians are our friends. They can be very helpful.