Ok, here's what I have...
PARAMETERS [Forms]![ParamMnthRpt]![Start] DateTime,
[Forms]![ParamMnthRpt]![End] DateTime;
SELECT Invest.NDate, Invest.NType, Invest.NArea, Invest.NDate AS Expr1,
Invest.NArea AS Expr2
FROM Invest
WHERE (((Invest.NDate) Between ([Forms]![ParamMnthRpt]![Start]) And
([Forms]![ParamMnthRpt]![End])) AND ((Invest.NType)="Vehicle Accident"));
This MonthlyChartQuery gives me the date of an accident, and the location
(building area, test road, farm road, off property). There are 25 records
in
this query. The expressions are added just to give me the same field
names
as in the MonthlyChartQueryLast, below...
PARAMETERS [Forms]![ParamMnthRpt]![Start] DateTime,
[Forms]![ParamMnthRpt]![End] DateTime;
SELECT Invest.NDate, Invest.NType, Invest.NArea, [NDate]+1826 AS Expr1,
"Previous Year" AS Expr2
FROM Invest
WHERE (((Invest.NDate) Between ([Forms]![ParamMnthRpt]![LastStart]) And
([Forms]![ParamMnthRpt]![LastEnd])) AND ((Invest.NType)="Vehicle
Accident"));
This query will show the date and location of accidents that happened 5
years earlier. There are 5 records in this query. Exp1 adds 5 years to
the
date shown, so a stacked column chart will only show a date range on the
x-axis of one year instead of five. (this will eventually be [NDate]+365,
but the only data entered other than 2009 so far is from 2004). Exp2
changes
all of the locations (building area, test road, etc.) to "Previous Year".
Here is where I am stuck. I need to make a third query to combine these
two
queries. There would only be 2 fields (Exp1 and Exp2) with a total of 30
records. The dates would all show from the same year (2009), and there
would
be a total of 5 locations (building area, test road, farm road, off
property,
Previous Year). From here I would base my chart off this third query.
the
x-axis would show a date range of Jan-Dec 2009, and the columns would show
some combination of the 5 locations. Then I can select the "Previous
Year"
data point on the column chart and change it's chart type to a line chart.
What I would be left with is a stacked column chart showing the actual
2009
accident locations totals by month, and a line chart on top of the columns
representing last years total monthly accidents as a trend line.
This even sounds confusing to me, so I hope you can make sense of it.
Thanks again for your help.
Duane Hookom said:
So, if I understand correctly, you want to chart 2 series. One is for
this
year and one for last. If so, it sounds like you have two queries that
have
the same date so you should be able to join them into a single query that
can be used as the Row Source.
If this is wrong, then come back with some SQL views of your queries and
other significant information.
--
Duane Hookom
MS Access MVP
I've actually simplified things in my question to what is really going
on,
but I did figure a way to do this, barring one problem. What I have
done
is
made a query of last years activities, and added an expression to add
365
days to the event date so everything displays as the current year. I
have
also added another expression to make all activities "Act4". This way,
it
shows up on the stacked column chart as one data point that I can
change
to a
line chart.. Now I have a query for the current year, and the
converted
query for the past year which is displayed as the current year. What I
would
like to do is combine the "ActDate" field from the first query with the
"ActDate" field from the second query, and do the same for the
"Activity"
fields of both queries. How can I make a third query so all of the
event
records from both queries are displayed in these two fields?
:
Can't really suggest anything without seeing the SQL view of your
chart's
Row Source. It might also help if you provided the Link Master/Child
properties.
--
Duane Hookom
MS Access MVP
Now to continue with the next problem. My stacked column chart is
working
great, each bar representing a month of total activities (Act1,
Act2,
Act3)
for the date parameters selected. On my Param form (for the user to
enter
the start & end dates) I added 2 more invisible text boxes to
display
the
same time frame one year earlier. What I would like to do is add a
line
chart onto the same chart to show the total activities from the same
time
frame of the previous year as a comparison. I understand that if I
can
somehow get last years total activities to show up as just another
part
of
the same columns, I can then change its chart type to a line chart.
What
I'm
not sure of, is how to add another field to the existing query for
the
previous years total activities, or to somehow add another query to
the
same
chart. Any help on how to proceed is appreciated.
:
Thank you Mr. Hookom, you guys saved me again. It works perfectly.
:
Typically a chart row source will be a crosstab query. If this is
the
case,
you must specify the data type of your parameters. Go to the
query
design of
the Row Source and select Query->Parameters and enter:
[Forms]![Param]![Start] Date/Time
[Forms]![Param]![End] Date/Time
--
Duane Hookom
MS Access MVP
I have a form titled "Param" for users to select a specific
year
of
data
to
view as a chart. The year is converted to start date and end
date
text
boxes. The "Date" criteria in the query reads "Between
([Forms]![Param]![Start]) And ([Forms]![Param]![End])" (without
the
quotes).
When I run the query, everything works fine. When I try to
open
the
chart
(form) based on this query I get an error message saying "The
Microsoft
Jet
Database engine does not recognize '[Forms]![Param]![Start]' as
a
valid
field
or expression." This chart is placed as a subform on a report
as
well,
but
first problems first.
I'm just a beginner with Access, but you guys have helped me do
some
cool
things in the past. I hope someone can help me with this
problem.
Thanks.
