cross tab confusion

[patti]

i inherited database. this query has me baffled.

TRANSFORM Sum([po qty]-[last rct qty]) AS [OO Qty]
SELECT [Description Table].Style, [Description Table].[Long Style], [On
Order].PO, [On Order].[New Req Date]
FROM ([On Order] INNER JOIN [Description Table] ON [On Order].[Long Style] =
[Description Table].[Long Style]) INNER JOIN [Month Table-Week Range] ON [On
Order].[New Req Date] = [Month Table-Week Range].DATE
WHERE ((([On Order].[New Req
Date])>=DateSerial(Year(DateAdd("m",-3,Date())),Month(Date())-3,1)))
GROUP BY [Description Table].Style, [Description Table].[Long Style], [On
Order].PO, [On Order].[New Req Date]
PIVOT DateDiff("m",Date(),[New Req Date])+IIf(DatePart("d",Date())>25,5,4)
In (1,2,3,4,5,6,7,8,9,10,11,12);

it want january's sum to end up in column 3 but it is ending up in column 5.
[new req date] will push to next month if day greater than the 25th.

thanks for any help.

 

[Rob Parker]

Hi Patti,

A cross-tab query will always place the fields listed in the SELECT clause
in the left-most fields, and then display the PIVOT fields. So, to force
January's data to column 3, you will have to remove two fields from the
SELECT clause (they can still appear in the GROUP BY clause). You can do
that in the query design grid by selecting "(not shown)" in the Crosstab row
of the grid for the fields you want to hide. You must show at least 1 Row
Heading field.

A workaround, if you must have all the Select fields shown (but with two of
them after the month fields), would be to base a new Select query on your
existing cross-tab query, and place the fields in the desired positions.

HTH,

Rob

PS. why does it matter which field position the result appears in?

 

[patti]

Thanks Rob.

I realize now how the cross-tab is working. The query feeds a report and
that's where the problem lies. Behind the report is code to refresh the data,
and then the controls on the report for the date columns are date-calculated.
Quite a mess to figure out.

Hi Patti,

A cross-tab query will always place the fields listed in the SELECT clause
in the left-most fields, and then display the PIVOT fields. So, to force
January's data to column 3, you will have to remove two fields from the
SELECT clause (they can still appear in the GROUP BY clause). You can do
that in the query design grid by selecting "(not shown)" in the Crosstab row
of the grid for the fields you want to hide. You must show at least 1 Row
Heading field.

A workaround, if you must have all the Select fields shown (but with two of
them after the month fields), would be to base a new Select query on your
existing cross-tab query, and place the fields in the desired positions.

HTH,

Rob

PS. why does it matter which field position the result appears in?

i inherited database. this query has me baffled.

TRANSFORM Sum([po qty]-[last rct qty]) AS [OO Qty]
SELECT [Description Table].Style, [Description Table].[Long Style], [On
Order].PO, [On Order].[New Req Date]
FROM ([On Order] INNER JOIN [Description Table] ON [On Order].[Long Style]
=
[Description Table].[Long Style]) INNER JOIN [Month Table-Week Range] ON
[On
Order].[New Req Date] = [Month Table-Week Range].DATE
WHERE ((([On Order].[New Req
Date])>=DateSerial(Year(DateAdd("m",-3,Date())),Month(Date())-3,1)))
GROUP BY [Description Table].Style, [Description Table].[Long Style], [On
Order].PO, [On Order].[New Req Date]
PIVOT DateDiff("m",Date(),[New Req Date])+IIf(DatePart("d",Date())>25,5,4)
In (1,2,3,4,5,6,7,8,9,10,11,12);

it want january's sum to end up in column 3 but it is ending up in column
5.
[new req date] will push to next month if day greater than the 25th.

thanks for any help.