Need help Designing Form

[exbaitman]

OK I have sales data entered in a DB like this. Each [] is a column.



[Saleman][Customer][Product Type][Month][Year][[Sales amount]

Our company reports sales monthly to see how people are doing. The sales figures are entered in the [Sales Amount] field. Then I have the [Year] and [Month] so I can filter. Month data is numerical (ex. 1,2,3 ect.)

My issue is this, i cannot for the life of me, figure out how to pull a report/form/query with the following structure. I put "" around the fields i want calculated.

[Salesman][Customer][Product Type] - "Sales from 01-2015" - "Sales from 01-2014" - "Percentage Difference"

I have been able to pull a cross-tab query where I calculate the Monthly Sales grouped by the [Salesman][Customer][Product Type]. That gives me 12 columns 01,02,03,04,05, ect. with that [Sales Amount] as the sum value. I cannot figure out how to get 01 (janurary) sales from 2014 in one column, and 01 sales from 2015 in another column. Because in the cross-tab query the [Year] is still a row, not a column.

The frustrating part is that if I pull a pivot table in excel, it takes two seconds to get what I want. Access 2013 doesn't seem to ahve this feature. But I don't want my end users using excel, I want to build a nice looking report for them to open and print so they can't screw it up.

[CJ_London]

with your crosstab query, create a new column Period:format([Month],"00") & "-" & [Year] and make this your column header instead of month - and remove year as a row heading

note month and year are reserved words, so using them as field names can cause unexpected problems

[exbaitman]

EDIT- Never mind my below question, I used the "sum" feature instead of the "first" feature and got my desired results. I am going to leave question for other just in case.

OK, I got that pulling the correct data in different columns. Thank you so much.

But, as usual when learning something new, that fix has created a new question.

It separated the items into columns, 01-2014,01-2015, 02-2014, 02-2015, ect. But each entry is its own row now. So the sales from 01-2014 are on a different row from the sales for 01-2015. I tried doing a "total" query and choosing"first" for these columns but that left out a lot of data.

Any idea how to shrink that back into one row?

OK I have sales data entered in a DB like this. Each [] is a column.

[Saleman][Customer][Product Type][Month][Year][[Sales amount]

Our company reports sales monthly to see how people are doing. The sales figures are entered in the [Sales Amount] field. Then I have the [Year] and [Month] so I can filter. Month data is numerical (ex. 1,2,3 ect.)

My issue is this, i cannot for the life of me, figure out how to pull a report/form/query with the following structure. I put "" around the fields i want calculated.

[Salesman][Customer][Product Type] - "Sales from 01-2015" - "Sales from 01-2014" - "Percentage Difference"

I have been able to pull a cross-tab query where I calculate the Monthly Sales grouped by the [Salesman][Customer][Product Type]. That gives me 12 columns 01,02,03,04,05, ect. with that [Sales Amount] as the sum value. I cannot figure out how to get 01 (janurary) sales from 2014 in one column, and 01 sales from 2015 in another column. Because in the cross-tab query the [Year] is still a row, not a column.

The frustrating part is that if I pull a pivot table in excel, it takes two seconds to get what I want. Access 2013 doesn't seem to ahve this feature. But I don't want my end users using excel, I want to build a nice looking report for them to open and print so they can't screw it up.