Joining the lines in a Line Chart with Null data

[Evi]

My Line chart shows Fuel usage by over several years.
The grid across the bottom (x axis) shows the month numbers The grid up the
left side shows the amount spent. Each line on the chart represents a
different year.

In November 2006 no fuel was purchased.
Instead of the line being joined from October 06 to December 06 (as one
would in a hand-drawn graph) there is a break in the line.
I can force all 12 month numbers to appear in the Row Headers of the
crosstab query on which the Row Source of the Chart is based by using a left
join with a query containing 2 other queries, one showing all the potential
months 1 to 12 and the other showing all the years in the database. but this
doesn't help because the Values are null

Is there any way I can force the datapoints to join?

Evi

 

[Evi]

You should force all the months to be present. You can use
IIf or the Nz function to convert Null to 0 so the rest of
the processing has a valid value.

It is not necessary to use a Left Join to get all 12 months.
Instead you can just list the month numbers in the crosstab
query's Column Headings property.

Thanks Marsh, but the month numbers are the Row Headings. The Column
Headings are the Year numbers. Each line in the line chart represents a
different year.

Using
Expenditure:NZ([MyValueField],0) did not work. The null values were still
Null.

Evi

 

[Marshall Barton]

My Line chart shows Fuel usage by over several years.
The grid across the bottom (x axis) shows the month numbers The grid up the
left side shows the amount spent. Each line on the chart represents a
different year.

In November 2006 no fuel was purchased.
Instead of the line being joined from October 06 to December 06 (as one
would in a hand-drawn graph) there is a break in the line.
I can force all 12 month numbers to appear in the Row Headers of the
crosstab query on which the Row Source of the Chart is based by using a left
join with a query containing 2 other queries, one showing all the potential
months 1 to 12 and the other showing all the years in the database. but this
doesn't help because the Values are null

You should force all the months to be present. You can use
IIf or the Nz function to convert Null to 0 so the rest of
the processing has a valid value.

It is not necessary to use a Left Join to get all 12 months.
Instead you can just list the month numbers in the crosstab
query's Column Headings property.

 

[Evi]

You should force all the months to be present. You can use
IIf or the Nz function to convert Null to 0 so the rest of
the processing has a valid value.

It is not necessary to use a Left Join to get all 12 months.
Instead you can just list the month numbers in the crosstab
query's Column Headings property.

Thanks Marsh, but the month numbers are the Row Headings. The Column
Headings are the Year numbers. Each line in the line chart represents a
different year.

Using
Expenditure:NZ([MyValueField],0) did not work. The null values were still
Null.

Evi

Perhaps it makes it clearer if I tell you (in summary) that the database
itself lists all the Income and expenses of my hubby's one-man gardening
business over the last 3 years.
Each year, I archive the previous year's data into an ArchiveTable in the
database.
I've used a Union Query to combine the expend
This Expenditure table contains ItemID which links to TblItems which
contains ItemID and Item. Item contains records like
Fuel, Vehicle Repairs, Stationary, Tools, Materials
To get the fuel expenditure, I filter ItemID in TblExpenditure so that only
the expenses on fuel are showing.
That is the only Item I want to show in my chart. I want to compare monthly
costs from one year to another.

I suppose I could solve this by adding a field to a query based on
TblExpenditure that says :

FuelExpense:IIF([ItemID] =6, [Expend], 0)

so that only the fuel expenditures are totalled while the other records will
contain 0.

I could use this field in my Crosstab Values section to get the total spent
on fuel for any month in the Expenditure table for that year.

But even this would not work if NO expenditure was made in any one month
(winter can be very lean in the gardening business!)

I was expecting NZ to work in the crosstab too, but it didn't

Evi

 

[Evi]

Evi wrote:

My Line chart shows Fuel usage by over several years.
The grid across the bottom (x axis) shows the month numbers The grid

up
the

left side shows the amount spent. Each line on the chart represents a
different year.

In November 2006 no fuel was purchased.
Instead of the line being joined from October 06 to December 06 (as one
would in a hand-drawn graph) there is a break in the line.
I can force all 12 month numbers to appear in the Row Headers of the
crosstab query on which the Row Source of the Chart is based by using

a
left

join with a query containing 2 other queries, one showing all the potential
months 1 to 12 and the other showing all the years in the database.

but
this

doesn't help because the Values are null


You should force all the months to be present. You can use
IIf or the Nz function to convert Null to 0 so the rest of
the processing has a valid value.

It is not necessary to use a Left Join to get all 12 months.
Instead you can just list the month numbers in the crosstab
query's Column Headings property.

Evi wrote
Thanks Marsh, but the month numbers are the Row Headings. The Column
Headings are the Year numbers. Each line in the line chart represents a
different year.

Using
Expenditure:NZ([MyValueField],0) did not work. The null values were still
Null.

I'm terrible with charts, but it should(?) be
straightforward once the query is properly designed.

I had the row/columns mixed up so now I think you will need
the 12 row table and the Left Join. The Nz or IIf should
then work.

Oh I've got the answer. You wouldn't believe how stupid I am! (I hope you're
reading this Jai!)
I went into that puzzling SQL view to copy that for my next message and saw
that putting my NZ around my expend field in the query grid gave me
Sum (Nz([Expend],0))

All I needed to do was to put the NZ in a different place in Sql view ie
NZ(Sum([Expend],0)) and all the nulls were nicely filled with 0s

Joy oh joys.
Thanks Marsh, you're a star.

Evi

 

[Marshall Barton]

You should force all the months to be present. You can use
IIf or the Nz function to convert Null to 0 so the rest of
the processing has a valid value.

It is not necessary to use a Left Join to get all 12 months.
Instead you can just list the month numbers in the crosstab
query's Column Headings property.

Thanks Marsh, but the month numbers are the Row Headings. The Column
Headings are the Year numbers. Each line in the line chart represents a
different year.

Using
Expenditure:NZ([MyValueField],0) did not work. The null values were still
Null.

I'm terrible with charts, but it should(?) be
straightforward once the query is properly designed.

I had the row/columns mixed up so now I think you will need
the 12 row table and the Left Join. The Nz or IIf should
then work.