Stacked area chart with multiple values in the x axis

[King_Black]

Hi All,



First message for me in this nice forum. I hope I can contribute in the future!

I spent hours trying to solve this problem without luck... Attached there is a super simplified version of the database that I'm currently using.

The database has been previously structured in a weird way. Basically the columns plans_week1, plans_week2, etc show the amount of hours spent by an employee in a week.

Is there a way to create a report with an area chart that shows the weeks (plans_week1, plans_week2,etc) in the x axis (like the one that I have made in excel)?

Thanks a lot!

[June7]

Remove the grouping from Query1, make just a normal SELECT joining tables.

Build a UNION query:

SELECT proj_type, 1 AS weekNum, plans_week1 AS Data FROM Query1
UNION ALL SELECT proj_type, 2, plans_week2 FROM Query1
UNION ALL SELECT proj_type, 3, plans_week3 FROM Query1
UNION ALL SELECT proj_type, 4, plans_week4 FROM Query1
UNION ALL SELECT proj_type, 5, plans_week5 FROM Query1
UNION ALL SELECT proj_type, 6, plans_week6 FROM Query1;

RowSource for chart:
TRANSFORM Sum([Data]) AS [SumOfData] SELECT [weekNum] FROM [Query2] GROUP BY [weekNum] PIVOT [proj_type];

[King_Black]

Hi June7, thank you very much for your answer. You're very kind!
How do you structure the union query if there would be an additional column in the Query1? "Disc_description" is supposed to be the "link" between the chart and the report itself.
Apologies if I didn't include it before.

[June7]

Just put the field name in each line.

How is it a link to the report? The chart uses a CROSSTAB query as RowSource. Review http://allenbrowne.com/ser-67.html#Param

[King_Black]

In the chart wizard, after I selected the area chart, If I want the chart to change from record to record, I should select the fields that link the document and the chart. The fields are supposed to be "Disc_description". Sorry but I'm not very familiar with access

[June7]

My charts have all been XYplots using a simple SELECT as RowSource, example: SELECT Size, Pass, Hi, Lo FROM GraphBMD WHERE LabNum=[tbxLabNum] ORDER BY Size;

[tbxLabNum] is reference to a control on report. This synchronizes the chart data with the report data. Never could figure out how to get the Master/Child Links properties of chart to work.

If you saved the CROSSTAB as an object, then the chart RowSource can be: SELECT weekNum, [Annual Leave (TOWP)], Backlog, [Internal Order activity], [Leave Without Pay], Overhead, [Proposal activity], Prospect FROM Query3;

I have never had to use CROSSTAB queries for any reason in my db's, not even charts. Lucky me. So I have never had to apply filter to CROSSTAB. Applying dynamic filter when CROSSTAB is involved requires use of PARAMETERS in query as explained by Allen Browne link.

[King_Black]

Solved! Thank you very much for your help mate!

[King_Black]

Last question actually: How can I change the order of the areas? Right now the areas are automatically ordered in alphabetical order but that doesn't suit my needs.
I can easily do that in the union query adding an additional parameter (like 1 to Backlog, 2 to Prospect, etc) but I can't figure out how to do so in the Row Source.
The parameter that I would like to use to order the chart areas it shouldn't be included in the chart though. Not sure if it is clear :-/

[June7]

Instead of using CROSSTAB as the chart RowSource, use the SELECT that pulls from the CROSSTAB. Order of areas will be determined by order the fields are referenced.

[King_Black]

Ok, but the CROSSTAB: TRANSFORM Sum([Data]) AS [SumOfData] SELECT [weekNum] FROM [Query2] GROUP BY [weekNum] PIVOT [proj_type];

gives me the Row heading (weekNum) and the columns (backlogs,prospects, etc). What should I use in the RowSource as value? And what should I use as PIVOT?

[King_Black]

Also How do I hard code some columns in the query? I need that as the charts colours are not consistent. This is because the some fields are populated with values in some Disc_description but others are not.
Jeez I'm going back to excel lol

[June7]

See post 6. Save the CROSSTAB as a query object. Then the chart RowSource is just a SELECT query.

What do you mean by hard code columns? You want to set colors? Have a look at https://www.accessforums.net/showthread.php?t=29178

[King_Black]

Problem solved. Thank you very much June7. I really appreciated it! My hope is that in the future I can become competent as you.

[June7]

Might find this interesting http://allenbrowne.com/ser-67.html#ColHead