Summing Counts for sorting?

R

rgrantz

I have one table w/ Order details (OrderNumber, etc.). I have another table
that has items on that order that were shipped late and why.

I have 2 main queries to help determine common problems; what items are
frequently shipped late, and what reasons are frequently causes of late
shipments. The SQL view of each query is at the end of this post.

I am trying to sort the reports I have on these queries by the Sum of Counts
of (Grouped) Items (from Query 1) and the Sum of Counts of (grouped) Reasons
(from Query 2). However, I can't sort by a calculated field. All of my
attempts to Sum these counts in the queries, however, have not worked. It
seems I can't Count and Sum the Count of Grouped fields in the same query.
What is the best way to go about this?

Ex. of report I want:

- Item #35623 (PartNum is top-level Group): Late 14 times
(=Sum([CountOfPartNum]); this one is the most times, so this is first record
on report)
- Because of (ReasonLate is 2nd Level group)
Not in Stock: 5
No Packaging: 5
No Excuse: 4

Etc...

Same report for Reasons, just grouping and field Counted is different.

Query 1: Counts number of times (grouped) items are late (calculating
"late" using turnaround between date received and date shipped, or no ship
date and today's date)

SELECT OrderLateParts.PartNum, Count(OrderLateParts.PartNum) AS
CountOfPartNum, OrderLateReasons.ReasonLateDescrip
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2)) OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateParts.PartNum, OrderLateReasons.ReasonLateDescrip;


Query 2: Counts number of times (grouped) reasons for being late are
entered:

SELECT OrderLateReasons.ReasonLateDescrip, Count(OrderLateParts.ReasonLate)
AS CountOfReasonLate, OrderLateParts.PartNum
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2)) OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateReasons.ReasonLateDescrip, OrderLateParts.PartNum;


I am looking not only for help on how to sort this right, but also any
advice you may have on poorly constructed queries, etc. I dabble in Access
development, but have a hard time wrapping my head around query design, and
I've been asked to do some relatively complex stuff. Thanks for any help
and/or advice, and please let me know if this post belongs in the report
newsgroup.


Thanks for reading.
 
G

Guest

Can this help you?
In this sql statement i combined 2 agregates.

SELECT Address, CusCnt, FndCnt
FROM (SELECT Address, Count(*) AS CusCnt FROM Customer GROUP BY Address)
CustomerCount
INNER JOIN (SELECT Address, Sum(Fund) AS FndCnt FROM Customer GROUP BY
Address) CustomerFund
ON CustomerCount.Address=CustomerFund.Address

- Raoul

rgrantz said:
I have one table w/ Order details (OrderNumber, etc.). I have another table
that has items on that order that were shipped late and why.

I have 2 main queries to help determine common problems; what items are
frequently shipped late, and what reasons are frequently causes of late
shipments. The SQL view of each query is at the end of this post.

I am trying to sort the reports I have on these queries by the Sum of Counts
of (Grouped) Items (from Query 1) and the Sum of Counts of (grouped) Reasons
(from Query 2). However, I can't sort by a calculated field. All of my
attempts to Sum these counts in the queries, however, have not worked. It
seems I can't Count and Sum the Count of Grouped fields in the same query.
What is the best way to go about this?

Ex. of report I want:

- Item #35623 (PartNum is top-level Group): Late 14 times
(=Sum([CountOfPartNum]); this one is the most times, so this is first record
on report)
- Because of (ReasonLate is 2nd Level group)
Not in Stock: 5
No Packaging: 5
No Excuse: 4

Etc...

Same report for Reasons, just grouping and field Counted is different.

Query 1: Counts number of times (grouped) items are late (calculating
"late" using turnaround between date received and date shipped, or no ship
date and today's date)

SELECT OrderLateParts.PartNum, Count(OrderLateParts.PartNum) AS
CountOfPartNum, OrderLateReasons.ReasonLateDescrip
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2)) OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateParts.PartNum, OrderLateReasons.ReasonLateDescrip;


Query 2: Counts number of times (grouped) reasons for being late are
entered:

SELECT OrderLateReasons.ReasonLateDescrip, Count(OrderLateParts.ReasonLate)
AS CountOfReasonLate, OrderLateParts.PartNum
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2)) OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateReasons.ReasonLateDescrip, OrderLateParts.PartNum;


I am looking not only for help on how to sort this right, but also any
advice you may have on poorly constructed queries, etc. I dabble in Access
development, but have a hard time wrapping my head around query design, and
I've been asked to do some relatively complex stuff. Thanks for any help
and/or advice, and please let me know if this post belongs in the report
newsgroup.


Thanks for reading.
Click to expand...
 
M

[MVP] S.Clark

To sort in a report, choose View / Sorting and Grouping from the main menu.
Add the fields that you want to group by, and also the fields that you would
like to sort by, once the grouping is completed.

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

rgrantz said:
I have one table w/ Order details (OrderNumber, etc.). I have another
table
that has items on that order that were shipped late and why.

I have 2 main queries to help determine common problems; what items are
frequently shipped late, and what reasons are frequently causes of late
shipments. The SQL view of each query is at the end of this post.

I am trying to sort the reports I have on these queries by the Sum of
Counts
of (Grouped) Items (from Query 1) and the Sum of Counts of (grouped)
Reasons
(from Query 2). However, I can't sort by a calculated field. All of my
attempts to Sum these counts in the queries, however, have not worked. It
seems I can't Count and Sum the Count of Grouped fields in the same query.
What is the best way to go about this?

Ex. of report I want:

- Item #35623 (PartNum is top-level Group): Late 14 times
(=Sum([CountOfPartNum]); this one is the most times, so this is first
record
on report)
- Because of (ReasonLate is 2nd Level group)
Not in Stock: 5
No Packaging: 5
No Excuse: 4

Etc...

Same report for Reasons, just grouping and field Counted is different.

Query 1: Counts number of times (grouped) items are late (calculating
"late" using turnaround between date received and date shipped, or no ship
date and today's date)

SELECT OrderLateParts.PartNum, Count(OrderLateParts.PartNum) AS
CountOfPartNum, OrderLateReasons.ReasonLateDescrip
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2))
OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateParts.PartNum, OrderLateReasons.ReasonLateDescrip;


Query 2: Counts number of times (grouped) reasons for being late are
entered:

SELECT OrderLateReasons.ReasonLateDescrip,
Count(OrderLateParts.ReasonLate)
AS CountOfReasonLate, OrderLateParts.PartNum
FROM (OrderOrders LEFT JOIN OrderLateParts ON OrderOrders.OrderNum =
OrderLateParts.OrderNum) LEFT JOIN OrderLateReasons ON
OrderLateParts.ReasonLate = OrderLateReasons.ReasonLateID
WHERE (((OrderOrders.DateRec) Between
[forms]![frmOrderReportDates]![StartDate] And
[forms]![frmOrderReportDates]![EndDate]) AND
((OrderOrders.ShippedComplete)=0) AND (([DateOrigShipped]-[DateRec])>2))
OR
(((OrderOrders.DateOrigShipped) Is Null) AND ((Date()-[DateRec])>2))
GROUP BY OrderLateReasons.ReasonLateDescrip, OrderLateParts.PartNum;


I am looking not only for help on how to sort this right, but also any
advice you may have on poorly constructed queries, etc. I dabble in
Access
development, but have a hard time wrapping my head around query design,
and
I've been asked to do some relatively complex stuff. Thanks for any help
and/or advice, and please let me know if this post belongs in the report
newsgroup.


Thanks for reading.
Click to expand...
 
Top