Please help writing correct query for my results!

[gangel]

Hey people!


Soooo im newish to access but learning fast. Getting great results but just got stumped... I have a feeling I need a cross tab query + Report with subreports + sub querys.... just want some advice before I lose myself in the organisation!


SO:
I have lots of items [stock], each has a [department] and a [retailprice]
Each [stock] is sold in a [saleID] which has its own [date]

I have created a query which gives me
[Date] + [Department] + month([date]) + Year([Date]) + [RetailPrice].

I have then created a second query targeted at the first

Grp(Year) + Group(Month) + Group (Department) + SUM(RetailPrice)

Giving me things like:
2014, January, Cough Medicine, $12,000

etc etc.

Im ok with this and happy with my direction.

But I want to end up with this


Now im lost. This above is just Month of January but in reality I want one of each othese (I would assume these are subreports) But im a bit lost how to go from here and making it into a query and report from there.

Any advice or direction would be awesome! Im trying to work it mainly myself but just stock on this concept.!

Regards,
Gangel

[CJ_London]

Within the context of a query, you cannot have your Jan row

If you were to group year and month together - e.g. to get 201001,201002 etc

you can use a crosstab with this column as the header column

[gangel]

mk...

I appreciate the input Ajax however I have also found a way around it but creating subreports..

So... I have created a report that is based on a crosstab query with Years going along the top.

Down the side are MONTHS and DEPARTMENTS.
Im going to hide the months column.

What I need to do is then have 12 of these reports running but the month field for each one different.

The only way I know how to do anything like that is creating a form and button that opens the report with wherecondition "[monthname] = "January""

This works, but how do I get 12 subreports to each refer to a different monthname??

Thanks again for your help

[CJ_London]

for each subreport, have a hidden unbound textbox and set the controlsource of that textbox to the name of your month

then in your subreport properties set the linkchild property to the name of your month field in the subreport and the linkmaster property to the name of the relevant hidden textbox

[gangel]

sorry bit confused.
The textbox in the the SUBreport should have [Month] as the control source (understand that)

But in the MAIN report what do I use to chosoe the months? I tried a textbox but when I try and link the report to the sub report it says cant link unbound reports

[CJ_London]

the hidden text box should be on your main form