Crosstab - Sort by Aggregate

G

Guest

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");

This crosstab query produces this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A A1 0 5 0
1 A B1 10 0 0
1 B A1 0 0 5
1 B B1 0 5 0

sorted ascending by team, then sewer, then case

I want it like this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A B1 10 0 0
1 A A1 0 5 0
1 B B1 0 5 0
1 B A1 0 0 5

sorted ascending by team, then sewer, then by first-available qty under date

any ideas?

thanks,
c
 
G

Guest

the problem is that I can't sort the aggregate function "Sum([Schedule Team
Printer - 1].Quantity)" and if I sort the date field, it lists the dates
backwards without regard for the case quantities.

[MVP] S.Clark said:
Add Descending to the sort of field Case.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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");

This crosstab query produces this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A A1 0 5 0
1 A B1 10 0 0
1 B A1 0 0 5
1 B B1 0 5 0

sorted ascending by team, then sewer, then case

I want it like this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A B1 10 0 0
1 A A1 0 5 0
1 B B1 0 5 0
1 B A1 0 0 5

sorted ascending by team, then sewer, then by first-available qty under
date

any ideas?

thanks,
c
Click to expand...
Click to expand...
 
D

Duane Hookom

TRY:

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 [Schedule Team Printer - 1].Team,
[Schedule Team Printer - 1].Sewer,
Sum([Schedule Team Printer - 1].Quantity) DESC
PIVOT Format([January].[Date],"mm/dd/yy");
 
G

Guest

Thanks Duane, I see where you're going- but I still get the same error message:

Cannot have aggregate function in ORDER BY clause (Sum([Schedule Team
Printer - 1] Quantity))

Duane Hookom said:
TRY:

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 [Schedule Team Printer - 1].Team,
[Schedule Team Printer - 1].Sewer,
Sum([Schedule Team Printer - 1].Quantity) DESC
PIVOT Format([January].[Date],"mm/dd/yy");


--
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");

This crosstab query produces this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A A1 0 5 0
1 A B1 10 0 0
1 B A1 0 0 5
1 B B1 0 5 0

sorted ascending by team, then sewer, then case

I want it like this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A B1 10 0 0
1 A A1 0 5 0
1 B B1 0 5 0
1 B A1 0 0 5

sorted ascending by team, then sewer, then by first-available qty under
date

any ideas?

thanks,
c
Click to expand...
Click to expand...
 
D

Duane Hookom

You can create you query as I suggested without the order by Sum(....).

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 [Schedule Team Printer - 1].Team,
[Schedule Team Printer - 1].Sewer,
PIVOT Format([January].[Date],"mm/dd/yy");

Then create a new query based on the crosstab that sorts including TotQty.

--
Duane Hookom
MS Access MVP


c said:
Thanks Duane, I see where you're going- but I still get the same error
message:

Cannot have aggregate function in ORDER BY clause (Sum([Schedule Team
Printer - 1] Quantity))

Duane Hookom said:
TRY:

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 [Schedule Team Printer - 1].Team,
[Schedule Team Printer - 1].Sewer,
Sum([Schedule Team Printer - 1].Quantity) DESC
PIVOT Format([January].[Date],"mm/dd/yy");


--
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");

This crosstab query produces this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A A1 0 5 0
1 A B1 10 0 0
1 B A1 0 0 5
1 B B1 0 5 0

sorted ascending by team, then sewer, then case

I want it like this:

Team Sewer Case 01/01/05 01/02/05 01/03/05, etc.
1 A B1 10 0 0
1 A A1 0 5 0
1 B B1 0 5 0
1 B A1 0 0 5

sorted ascending by team, then sewer, then by first-available qty under
date

any ideas?

thanks,
c
Click to expand...
Click to expand...
Click to expand...
 

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

Access Running Balance in Access 1
Running Balance (Debit and Credit) in Access 2007 4
Fill in the date gaps 4
Is this possible 3
Counting Table (By Date) 2
Lookup part of a cell's contents and return value of entire cell 2
crosstab query 2
Update Query - Consecutive Numbers 9

Top