Area Chart without individual data point

[fnzeee]

I have a table of data with the following fields:

Resource Name 1, Start Date 1, End Date 1, % Allocated 1, Resource Name 2, Start Date 2, End Date 2, % Allocated 2, Resource Name 3, Start Date 3, End Date 3, % Allocated 3

From this I am trying to create an area chart that shows the % allocated BY MONTH that a resource is allocated. So the graph would show the % allocated for Resource 1 between resource 1's start and end dates. It would show the same for resource 2 and for resource 3.

The issue is that the chart object expects that each record is a data point. In this case there would be many data points for each record (one for each month that occurs between the start and end date for that resource). I need to build a query or configure the chart object to extrapolate data points (% allocated per month for Resource 1, 2 and 3) to use for the graph. Any suggestions on the best way to accomplish this? Any help is appreciated - I am dead in the water.

 

[jal]

I know nothing about charts, but a little about queries. One mistake that people make (I don't know if this is true of you) is trying too hard to do it all in one query. This leaves them stumped. Instead, break it up into smaller steps, and then you will probably find, at the end, a way to combine all the individual queries into one large query.

You apparently need a way to convert one record into multiple data points. Here's a suggestion on how to do that. Have a table called Periods


StartDate EndDate Period
01/01/2008 02/01/2008 January
02/01/2008 03/01/2008 February


And so on. Then join this table to your table, which I presume to be called Resources:


SELECT * INTO DataPoints
FROM
(




SELECT ResourceName1, Period, "Resource1" as Resource
FROM Resources as R
INNER JOIN Periods as P
ON P.StartDate <= R.StartDate and R.EndDate < P.endDate

UNION

SELECT ResourceName2, Period, "Resource2" as Resource
FROM Resources as R
INNER JOIN Periods as P
ON P.StartDate <= R.StartDate and R.EndDate < P.endDate

UNION

SELECT ResourceName3, Period, "Resource3" as Resource
FROM Resources as R
INNER JOIN Periods as P
ON P.StartDate <= R.StartDate and R.EndDate < P.endDate

)

Maybe that would give you a list of data points. On the other hand I wouldn't know the first thing about charting those points.

 

[jal]

Well, I usually don't get it right on the first try, and this is no exception. It occurred to me last night that my logic won't return all the data points because it doesn't fully cover each of the date spans. I was too tired to rethink this.

My ON clause was too simplistic. It will need some OR clauses to cover all the spans. Something like this, perhaps:

'Case where the resource start date (alone) falls within a month-period
ON (P.StartDate <= R.StartDate and R.StartDate < P.EndDate)
'Next, case where the resource startdate AND endDate faill within that month.
OR (P.StartDate <= R.StartDate and R.EndDate < P.EndDate)
'Next, case where the resource end date falls within that month.
OR (P.StartDate <= R.EndDate and R.EndDate < P.EndDate)
'Finally, the converse, the case where the month-period falls within the
'resource period
OR (R.StartDate <= P.StartDate and P.EndDate < R.EndDate)

The problem is that you should NEVER use an OR clause as part of an ON clause because Access 2003 doesn't optimize this query. With 40,000 records, in my case it was taking about 3 minutes to run the query - and only about 3 seconds when I eliminated the OR. (Even sql Server 2005 doesn't optimize for such an OR).

You can use UNIONs to elinate the OR clauses. For example the query:

SELECT * FROM tblEmp
INNER JOIN tbl_HoursWorked
ON tblEmp.EmpID = tbl_HoursWorked.EmpID
OR tblEmp.SSN = tbl_HoursWorked.SSN

Can be rewritten as a UNION:

SELECT * FROM tblEmp
INNER JOIN tbl_HoursWorked
ON tblEmp.EmpID = tbl_HoursWorked.EmpID


UNION


SELECT * FROM tblEmp
INNER JOIN tbl_HoursWorked
ON tblEmp.SSN = tbl_HoursWorked.SSN

 

[fnzeee]

Thank you for the quick and detailed replies. I will give this a try tomorrow when I get back into the database.