Force query to pick up 'empty' months 1

[Darrylles]

Hi,

I need to format an MS Access / MS Graph 11 chart correctly - without much success...

I have a query that generates total counts of order status' for every month of the 12 months preceding the month entered by the user.

Order status' = Early, OnTime, Late and Outstanding.

Sometimes, there are zero orders for a supplier in a particular month.

My aim: on the bar-chart I would like the months with no orders to leave a gap AND also state '0' within the displayed data-grid. However, it is currently displaying the bars correctly spaced (with gaps), but the datagrid is showing only cells WITHOUT months where the counts are zero. So the datagrid columns are not lining up with the bars.

I'm sure that I need to force the query to pick up all months (with zero counts), but am at a loss as to how to do this.

Any help appreciated.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Crowley16]

what's the query behind the result set?

the reason there's no results is because it's a null and those are filtered out. To achieve this, change your query to bring back 0s instead of nulls.

--------------------
Procrastinate Now!

 

[Darrylles]

Hi C,

The query isn't bringing back nulls, it's bringing back no records for some months, sometimes. i.e. if this was say over 3 months, with only 1 record per month, then I would get:
[tt]
Jan Feb Mar
1 1 1
[/tt]
If Feb had no record, then I still want:
[tt]
Jan Feb Mar
1 (null) 1

but am getting:

Jan Mar
1 1
[/tt]
I.E. If it can't find a record in February, then it won't bring the Month of February back - so it can't display 'Feb'.

The query itself is quite large (calculations that divide quantities into groups 0-3, 31-60,61-90,91-120 days late and early deliveries), and it feeds of another query that produces calculations. I think too large to paste here.

ATB

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SkipVought]

Make a dummy table with a column containing all the months. Join using the dummy table.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Darrylles]

Hi skip,

[tt]
SELECT Format([contractdeliv],"mmmyyyy") AS YearMonth, Sum((IIf(IsNull([EARLY]),0,[EARLY]))) AS EARLY_, Sum((IIf(IsNull([ONTIME]),0,[ONTIME]))) AS ONTIME_, Sum((IIf(IsNull([LATE]),0,[LATE]))) AS LATE_, Sum((IIf(IsNull([OUTS]),0,[OUTS]))) AS OUTS_
FROM qryDeliveries_All_Historical_Confirmed
GROUP BY Format([contractdeliv],"mmmyyyy"), Year([contractdeliv]), Month([contractdeliv])
ORDER BY Year([contractdeliv]), Month([contractdeliv]);
[/tt]

This is the chart record source (which feeds off another query, which in turn feeds off another query).
The year() and month()results are the headers for the 12 month periods.

How do I join the dummy table to this?

Thanks for response.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SkipVought]

You didn't say anything about YEAR in the lead up! Format([contractdeliv],"mmmyyyy")


But you DID state, "...on the bar-chart I would like the months with no orders to leave a gap AND also state '0' within the displayed data-grid."

Query/Plot DATE, not your mmmyyyy string. In the chart options, I believe you ought to be able to designate the x-axis as a Date/Time axis rather than a Category axis. The x-axis as Date/Time will then poistion the data horizontally in accordance with the Date Value, rather than the mmmyyyy Postion as Category.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Darrylles]

Hi Skip,

To have sequential months over a '12 month period', I must obviously have to know the year, and as the 'user entered' - '12 month period' can span years - I obviously also have to display the year (for viewer clarity).

Sorry, you know how it is - it seems obvious to yourself after you have been working on it for days ;-)

Anyway, I basically display 'Nov2006', 'Dec2006', 'Jan2007', 'Feb2007', and this is how the chart 'knows' about each column.

I don't really care if there IS no heading when there is no data, as long as a gap is left in BOTH the chart bars AND the data-grid column is empty (0 if possible), where an expected month would usually display.

This is a complicated query, which I intentionally tried to simplify in order not to cloud the issue.

I'm a bit confused by your 'query/plot date', as I'm grouping on this 'header' value - wouldn't this possibly give me 365 groups, and thus possibly 365 bars?

(Sorry - I'm au-fait with SQL, but definitely no expert).

Thanks for your time.

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SkipVought]

"query/plot date" query and plot a REAL DATE not a mmmyyyy STRING.

You'll have to plot a line, scatter or area rather than columns, using the Date/Time x-axis.



Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Darrylles]

Hi Skip,

But I'm splitting columns into sections for numbers of orders 0-30, 31-60, 61-90, 91-120 days early (above the axis) and the same for days late below the axis (as per the thread that you initially responded to).

As a side-note, this worked when I used the same Microsoft Graph 11 with Excel - passing Excel the query results (to a range). It worked because the range was defined: i.e. 12 rows.

So, what I need to do is FORCE the query to produce 'YearMonth' when no records for that 'YearMonth' exist, I've just no idea what angle to tackle this from.

I'm determined not to go back to using Excel to do this, the application MUST be self-contained. Excel was always simply a quick workaround.

Thanks for your responses either way - deserves a star.

ATB

Darrylle








Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[maxhugen]

G'day Darylle

Don't know if you can utilise a Crosstab query in your situation... but in a crosstab you can specify Column Headings for the field you designate to become columns.

For example, if you were crosstabbing by the days of the week (eg format([myDate],"ddd"), you can ensure all day columns display regardless of data by setting the Column Headings to "Sun","Mon","Tue","Wed","Thu","Fri","Sat".

Max Hugen
Australia

 

[Darrylles]

Hi max,

I have used crosstabs before a little.
What happens if the user entered Wednesday as the last day?
Would the headings display Thursday through to Wednesday dynamically?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[maxhugen]

Hi Darylle

No, if the Headings are set, that's how they will display.

It would be possible to open the report (via code) in design view, and dynamically change the Headings to "Thu","Fri","Sat","Sun","Mon","Tue","Wed" (etc) using code, then actually run the report.

To avoid that, is it possible to offer the user a selection for a "Week Ending" date, so that you can stick to one format?

Max Hugen
Australia

 

[bjb123]

I think they put you on the right track with a dummy table but I think you need to finish it with a union query.

The dummy table should have the exact same fields and format that are outputted from the final query. Write the code that deletes the old contents of the dummy table then loops through and insert a new record for each month needed at runtime based on the user input. Just make sure that all the other fields are set to zero, not null.

Then union the final query with that dummy table to create the chart.