Crosstab Queries raw maximum value (1 Viewer)

[MAAS8000]

I make a crosstab query the items in the raw header and months in column header, quantity is the value.
I set column for maximum qty of months.
the problem appear if one item duplicated in one month, access take the maximum of individual value not for the summation of every month.

 

[theDBguy]

Hi. It might help if you could post some images to illustrate the situation and problem.

 

[MAAS8000]

i hope this photo is clear

 

[June7]

Show sample raw data as well as the expected output.

 

[MAAS8000]

i have edited it

should be 96 & 104 but access get the maximum value from the table in this date
96 is sum of two data 63 + 33
104 is sum of 67 + 63

 

[June7]

Sorry, don't understand the formula. Where does 33 come from? Why add 63 to 67?

There is no raw data for us to understand and analyze.

 

[isladogs]

96 is sum of two data 63 + 33
104 is sum of 67 + 63

Ummm...no.
67+63=130

 

[MAAS8000]

sorry, i hope this is clear

 

[June7]

Why do you replicate date info in both tables? I don't even understand why you have 2 tables.

You want to show the max month for each Code (the Max of a Sum)? I don't think can accomplish that in one query.

Query1:
SELECT [CONSUMP-QTY].code, [CONSUMP-QTY].namee, TblConsumpDetail.MonthYear, Sum([CONSUMP-QTY].qty) AS SumQty
FROM TblConsumpDetail INNER JOIN [CONSUMP-QTY] ON TblConsumpDetail.DetailID = [CONSUMP-QTY].DetailID
GROUP BY [CONSUMP-QTY].code, [CONSUMP-QTY].namee, TblConsumpDetail.MonthYear;

Query2:
TRANSFORM Max(Query1.SumQty) AS MaxOfSumQty
SELECT Query1.code, Query1.namee, Max(Query1.SumQty) AS MaxMon
FROM Query1
GROUP BY Query1.code, Query1.namee
PIVOT Query1.MonthYear;

 

[MAAS8000]

thanks
I solved it
I made the month summation in the first query then the crosstab next