Display 12 months - even with no data for some months ...

[Darrylles]

Hi,

I'm basically looking for an angle to attack this from - not an immediate solution.
This is a greatly simplified example of my problem, but believe that a solution to this will give me a solution to the complicated version.

Table: table1
date_field
status_field

The status_field can be:
Early
OnTime
Late
Outstanding
(Status is basically the delivery status of an order).

A parameter query gets an 'end-month' from user, which stipulates the end-month of a 12 month period required. The query feeds a chart.
The chart MUST display a 12 column datagrid even if there are no orders for a particular month.
Each vertical bar on the chart MUST be aligned with the related datagrid column.

The query retrieves all records for the previous 12 months from the month entered by the user, but, say - Feb doesn't have an order; only 11 bars will be displayed (as expected, but no gap between Jan and Mar AND the datagrid only displays 11 columns - expanded to fill the space of 12. This is not what is required.

I want (in this scenario) 11 bars with one space for Feb, and the datagrid displaying 12 columns with no data (or zeros) where the Feb column should be.

Each column on the chart has a label: month/year. This is not key in the solution, the displaying of the 'empty' period is.

I'm pretty well resigned to the fact that the query that took me months to finesse to this point will have to be discarded, and that the solution resides in the first query that 'feeds' the rest (in the complicated version).

Any / all help appreciated.

3 stars guaranteed for this. I have thoroughly searched the 'Net and of course Tek-Tips - the solution has avoided me for many 'man-week-hours'.

Make my year please.

ATB

Darrylle


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

 

[PHV]

Why not having an helper table with all month/year values and use it with an outer join ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

I gave you a solution in another forum

Here's a sample of my data
[tt]
Date Amt
7/12/07 2
9/14/07 4
[/tt]
here's what my chart looks like
[tt]
5|
| ---
| ---
| --- ---
| --- ---
+----------------------
Jul 07 Aug 07 Sep 07
[/tt]
with the proper chart & axis settings.

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]

Thanks PHV and well done on the milestone,

Skip - your solution was for me to change the chart to a line / scatter graph, (in THIS forum) and, as I told you - my eventual chart was to display graph columns that are split into individual status's - which indicate counts of each status.

Why on earth have you hijacked this thread with a solution that I have already told you was unacceptable, claiming that you'd already solved it!!!!!!

I started this intentionally simplified thread, and you have complicated it already.

Please re-read my last post properly - you responded to the first without reading the content. Now you are still claiming a solution!

PHV - sorry for that, I may seem unappreciative, but believe me it's difficult enough to try to get this complicated requirement across to you guys without authorititive decisions from 'speed' readers.
(I think it's your fault PHV - they're trying to catch up with you).

It seems like I will have to re-write the queries that took an age to complete, and feed the sequence of queries with the initial 'static' months (somehow), but before I do that - I think that I will spend some time on compiling the question - with query content in there, and start one more 'one hit' thread.

I'll contact Tek-Tips to remove current related threads to clean up after.

ATB

Darrylle


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

 

[Darrylles]

Skip,

What happens when there is an additional record dated: 07/13/07?

ATB

Darrylle



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

 

[SkipVought]

SUM the values and Groub By month

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]}

 

[SkipVought]

BTW, it WILL work with COLUMN chart types.

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]}