crosstab q

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a crosstab query with my value field (product qty) is
aggregated by "sum". i need this field sorted in decending order, but access
will not let me do it. is there any way around this?

this is what I have now:
name product yesterday today tomorrow
tom x 0 0 10
tom y 0 5 0
tom z 2 0 0

this is what I want:
name product yesterday today tomorrow
tom x 2 0 0
tom y 0 5 0
tom z 0 0 10

thanks,
c
 
Your two datasets don't match. Which field do you want to sort on? What is
the sql of your query?
 
sorry, it should be: x = 10, y = 5, z = 2. my crosstab currently sorts the
products alphabetically, i want to sort in decending order of qty, regardless
of product name.

it is also important that the dates stay in consecutive order as well...
yesterday, today, tomorrow.

thanks,
c
 
There is no field named "qty" in your sample and you failed to provide the
SQL view of your crosstab.
 
TRANSFORM Sum([Schedule Team Printer - 1].[Quantity]) AS SumOfQuantity
SELECT [Schedule Team Printer - 1].[Team], [Schedule Team Printer -
1].[Sewer], [Schedule Team Printer - 1].[Case]
FROM January LEFT JOIN [Schedule Team Printer - 1] ON
[January].[Date]=[Schedule Team Printer - 1].[Date]
GROUP BY [Schedule Team Printer - 1].[Team], [Schedule Team Printer -
1].[Sewer], [Schedule Team Printer - 1].[Case]
ORDER BY Format([January].[Date],"mm/dd/yy")
PIVOT Format([January].[Date],"mm/dd/yy");
 
Now I don't know how you define your column "qty" since your SQL doesn't
return such a field. Also, you don't have columns defined as Yesterday,
Today, or Tomorrow. You can play with this SQL.

TRANSFORM Sum([Schedule Team Printer - 1].[Quantity]) AS SumOfQuantity
SELECT [Schedule Team Printer - 1].[Team],
[Schedule Team Printer - 1].[Sewer],
[Schedule Team Printer - 1].[Case],
Sum([Schedule Team Printer - 1].[Quantity]) As TotQty
FROM January LEFT JOIN [Schedule Team Printer - 1] ON
[January].[Date]=[Schedule Team Printer - 1].[Date]
GROUP BY [Schedule Team Printer - 1].[Team], [Schedule Team Printer -
1].[Sewer], [Schedule Team Printer - 1].[Case]
ORDER BY Sum([Schedule Team Printer - 1].[Quantity]) DESC
PIVOT "D" & DateDiff("d". [January].[Date], Date()+1) IN ("D2","D1","D0");


--
Duane Hookom
MS Access MVP


c said:
TRANSFORM Sum([Schedule Team Printer - 1].[Quantity]) AS SumOfQuantity
SELECT [Schedule Team Printer - 1].[Team], [Schedule Team Printer -
1].[Sewer], [Schedule Team Printer - 1].[Case]
FROM January LEFT JOIN [Schedule Team Printer - 1] ON
[January].[Date]=[Schedule Team Printer - 1].[Date]
GROUP BY [Schedule Team Printer - 1].[Team], [Schedule Team Printer -
1].[Sewer], [Schedule Team Printer - 1].[Case]
ORDER BY Format([January].[Date],"mm/dd/yy")
PIVOT Format([January].[Date],"mm/dd/yy");


Duane Hookom said:
There is no field named "qty" in your sample and you failed to provide
the
SQL view of your crosstab.
Click to expand...
Click to expand...
 
Back
Top