Sum product convert to access

[c.panteli@hartziotis.com.]

Hi I am new to access and I have the following issue.


I have the following Table representing the sales for N number of years. There is N number of Customer that can buy N number of N Item Codes in N month. Its a combination of Customer, product code, month and year. In Excell I can use Sum Product Expression but due to the number of different combinations I would like to wotk with ACCESS and build a DataBase that will Automatically Handle these Combinations. Tabels here below give an example of the "raw data table " and the requested "Process Data table".

RAW DATA TABLE.

YEAR	MONTH	CUSTOMER CODE	ITEM CODE	QTY	VAL
2012	1	CAP01	IT0001	1000	1250
2012	1	CAP01	IT0001	1000	1250
2012	1	CAP01	IT0001	1000	1250
2012	1	CAP01	IT0001	1000	1250
2013	2	CAP01	IT0001	1000	1250
2012	2	CAP01	IT0001	850	1000
2012	2	CAP01	IT0001	850	1000
2013	1	CAP01	IT0001	850	1000
2012	1	CAP02	IT0002	100	120
2012	1	CAP02	IT0002	1000	1200
2013	1	CAP02	IT0002	500	520
.	.	.	.	.	.
.	.	.	.	.	.
.	.	.	.	.	.
.	.	.	.	.	.
.	.	.	.	.	.
N - Years	N - months	N - customer codes	N - items Codes	N - QTD	N - Value

PROCESS DATA TABLE:

2012 2013 2012 2013 CUSTOMER CODE ITEM CODE MONTH 2012Q 2013Q 2012V 2013V DQ DV CAP01 IT0001 1 4000 850 5000 1000 -3150 -4000 CAP01 IT0001 2 1700 1000 2000 1250 -700 -750 CAP02 IT0002 1 1100 500 1320 520 -600 -800 CAP01 IT0001 4 0 0 0 0 0 0 CAP01 IT0001 5 0 0 0 0 0 0 CAP01 IT0001 6 0 0 0 0 0 0 CAP01 IT0001 7 0 0 0 0 0 0 CAP01 IT0001 8 0 0 0 0 0 0 CAP01 IT0001 9 0 0 0 0 0 0 CAP01 IT0001 10 0 0 0 0 0 0 CAP01 IT0001 11 0 0 0 0 0 0 CAP01 IT0001 12 0 0 0 0 0 0
Can anyone came across this issue before. What is the best way to build up the Data Base in order to handle such data calculations? Regards C

[June7]

A CROSSTAB query should be able to produce the output. Review http://allenbrowne.com/ser-67.html#MultipleValues

[c.panteli@hartziotis.com.]

Thank you for the information you had sent me. I have use CROSSTAB to build my query. I managed to SPLIt Sales Values for customer/item bougt/month/year. On the Same query I am trying to add also the QTD but I am getting the following message:

"to create a crosstab query, you must specify one or more heading row(s) options, one heading column option, and one value option"

How i can overcome this and also how I can add on the ame query the Variance between two year, in terms of sales and Qtd?

Thnaks

C

[June7]

Two ways I can see to use CROSSTAB and get the data transposed.

1. Allen Browne's method
Create a table named tblXtabColumns with field named FieldName and two records for Qty and Val. Build query:
TRANSFORM Sum(IIf([FieldName]="Qty",[Qty],[Val])) AS TheValue
SELECT CustomerCode, ItemCode, [Month]
FROM tblXTabColumns, Table1
GROUP BY CustomerCode, ItemCode, [Month]
PIVOT [FieldName] & [Year];

Do another query with the crosstab as source to calc the DQ, DV values.

2. Other
Create 2 Crosstab queries and then join the crosstabs.

TRANSFORM Sum(Table1.Qty) AS SumOfQty
SELECT Table1.CustomerCode, Table1.ItemCode, Table1.Month
FROM Table1
GROUP BY Table1.CustomerCode, Table1.ItemCode, Table1.Month
PIVOT Table1.Year;

TRANSFORM Sum(Table1.Val) AS SumOfVal
SELECT Table1.CustomerCode, Table1.ItemCode, Table1.Month
FROM Table1
GROUP BY Table1.CustomerCode, Table1.ItemCode, Table1.Month
PIVOT Table1.Year;

SELECT CrosstabQTY.CustomerCode, CrosstabQTY.ItemCode, CrosstabQTY.Month, CrosstabQTY.[2012], CrosstabQTY.[2013], CrosstabVAL.[2012], CrosstabVAL.[2013], [CrosstabQTY].[2013]-[CrosstabQTY].[2012] AS DQ, [CrosstabVal].[2013]-[CrosstabVal].[2012] AS DV
FROM CrosstabVAL INNER JOIN CrosstabQTY ON (CrosstabVAL.CustomerCode = CrosstabQTY.CustomerCode) AND (CrosstabVAL.ItemCode = CrosstabQTY.ItemCode) AND (CrosstabVAL.Month = CrosstabQTY.Month);

[c.panteli@hartziotis.com.]

Thank you very much.

It works - perfect, nice job.

Thanks Again

[c.panteli@hartziotis.com.]

Another one question,

I am validating the data for the DQ and DV - the results are wrong. This I think has to do with Null Cells. how can i overcome this. I have tried NZ function, it works but when I am trasfering the data to PowerPivot table the queries are not identified. What other options I have?

Last but not least on the Allen Browns method how I can calculate the Difference for Qtd and Year

Thanks

C

[June7]

Don't know what you mean by 'transfer to PowerPivot table'. I've never used Pivot tables or Pivot charts.

Don't know what you mean by 'Difference for Qtd and Year".

[c.panteli@hartziotis.com.]

The formula for calculating dq is this one [CrosstabQTY].[2013]-[CrosstabQTY].[2012] if one of the two values is empty the result is an empty cell i.e. 20 - emptycell gives empty cell.

[June7]

Yes, arithmetic with null results in null.

Use Nz() function. However, I don't know much about PivotTable and PivotQuery.

[c.panteli@hartziotis.com.]

Thank you for all tha valuable information you have gave me.

Everything works - fantastic.

C