Graphing Periodic Data

[kb3fed]

I have a database that is used to track total cost for projects. So, my
highest (hierarchial) table is the list of projects, the next table contains
the budget lines, and the last table contains updated costs for each budget
line. I would like to view the data in a cross-tab query or graph the data
either using a pivot chart: the same problem occurs in both.

Since the cost (entries in the cost update table) always stay the same
between updates (entries), some budget line costs get updated when others
don't. My problem in the cross-tab query is that some values are missing:
obviously since I never entered a cost update.

My question is, how do I have access populate the cross-tab query to have
all instances of null value equal to the previous entry?

A bigger question is, how do I get this to work with the pivot chart, or
even if this is the best approach for viewing the data.

 

[DM]

have you considered a query with an if statement as your actual final cost
then cross tab that query.
I'm not familiar with if statement logic but can imagine it working.

 

[Gary Walter]

I have a database that is used to track total cost for projects. So, my
highest (hierarchial) table is the list of projects, the next table
contains
the budget lines, and the last table contains updated costs for each
budget
line. I would like to view the data in a cross-tab query or graph the
data
either using a pivot chart: the same problem occurs in both.

Since the cost (entries in the cost update table) always stay the same
between updates (entries), some budget line costs get updated when others
don't. My problem in the cross-tab query is that some values are missing:
obviously since I never entered a cost update.

My question is, how do I have access populate the cross-tab query to have
all instances of null value equal to the previous entry?

A bigger question is, how do I get this to work with the pivot chart, or
even if this is the best approach for viewing the data.

Hi kb3fed,

I would probably tackle this problem
by producing a temp table of how I would
like the data to be ideally -- to feed to the crosstab
(including those null values).

Then, I would run an update query
to fill in those null values.

You can "hide" aggregation in the WHERE
clause of an update query.

If you would like some help with this update
query, provide some sample data of this
"ideal temp table."

In general, you would bring two instances
of the temp table

"M" the main instance
"P" the past instance

join the instances on "what in past
will give you a 'range' of correct past values
for main null value."

then, in WHERE clause use an aggregate
correlation subquery to get the very last entry.

this will probably miss something, but in general...

UPDATE
tblTemp AS M
INNER JOIN
tblTemp AS P
ON
M.Project = P.Project
SET
M.Cost = P.Cost
WHERE
M.Cost IS NULL
AND
P.ProjDate =
(SELECT
Max(q.ProjDate)
FROM
tblTemp AS q
WHERE
q.Cost IS NOT NULL
AND
q.ProjDate < M.ProjDate);

is it clear how this would work?

tblTemp
Project ProjDate Cost
A 1/1/2008 3400.00
A 2/1/2008
A 3/1/2008 2300.00

so when query is looking at "main"
record in M instance whose Cost is null,
it gets the Cost from the "previous" record
in P whose ProjDate is the latest date
that occurred before main date and had
a non-null Cost (3400.00).

Well...a general strategy for a general question...

crosstabs tend to choke on subqueries,
so your best strategy is almost always
to shape your data in an "ideal temp table"
first.

good luck,

gary