cross tab confusion

P

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.
 
R

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?
 
P

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.

Rob Parker said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Show All Dates 1
duplicate records 2
How to make a module out of a query 4
duplicate results in query 6
SUM in a UNION query 2
Query - Table 3
return without gosub error 0
Sum means empty query? 5

Top