Count in Query

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

David,

It might look something like this:

SELECT WO.JobNumber,
Count(WO.WorkOrderID) as OrdersRaised,
SUM(IIF(WO.CompletionDate = WO.Deadline, 1, 0)) as OnTime,
SUM(IIF(ISNULL(RI.ResponseInvoiceID), 0, 1)) as Invoiced,
SUM(IIF(ISNULL(RI.ResponseInvoiceID), 1, 0)) as AwaitingInvoice
FROM WorkOrder WO
LEFT JOIN ResponseInvoice RI
ON WO.JobNumber = RI.JobNumber
GROUP BY WO.JobNumber

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

It didn't occur to me that there might be more than one invoice for each WO.

--
Email address is not valid.
Please reply to newsgroup only.


"Ken Sheridan" wrote:

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

The invoices seem to reference JobNumber which is a non-key column in the
work orders table, which implies this. It also points to a flaw in the
logical model, I think.

Ken Sheridan
Stafford, England

"Dale Fye" wrote:

It didn't occur to me that there might be more than one invoice for each WO.

--
Email address is not valid.
Please reply to newsgroup only.


"Ken Sheridan" wrote:

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

Ken, I've tried your solution but get a syntax error in the two subqueries
between:

(SELECT COUNT *

and

WO2.JobNumber))

I'll try and figure it out.

Dale, yours almost works, but I get OrdersRaised and Invoiced counted twice.
I had this problem previously when including both the WorkOrder and
ResponseInvoice tables.

Regarding JobNumbers etc. Every contract we receive is given a JobNumber. I
have another table that stores the jobs. JobNumber is a foreign key in
WorkOrder, and a foreign key in ResponseInvoice. Every JobNumber can have
many WorkOrders, every JobNumber can have many ResponseInvoices.
ResponseInvoice is not related to WorkOrder because an invoice can be created
as a seperate entity (ie, an invoice with no related WorkOrder). For
accounting purposes, every invoice must have it's associated JobNumber
included (as does every purchase order, timesheet etc etc etc).

Anway, thanks for all your help.

Dave

"Ken Sheridan" wrote:

The invoices seem to reference JobNumber which is a non-key column in the
work orders table, which implies this. It also points to a flaw in the
logical model, I think.

Ken Sheridan
Stafford, England

"Dale Fye" wrote:

It didn't occur to me that there might be more than one invoice for each WO.

--
Email address is not valid.
Please reply to newsgroup only.


"Ken Sheridan" wrote:

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

Dave:

Mea culpa; the asterisk should be within parentheses in the first subquery:

SELECT COUNT(*)

but not in the second:

SELECT *

Ken Sheridan
Stafford, England

"David M C" wrote:

Ken, I've tried your solution but get a syntax error in the two subqueries
between:

(SELECT COUNT *

and

WO2.JobNumber))

I'll try and figure it out.

Dale, yours almost works, but I get OrdersRaised and Invoiced counted twice.
I had this problem previously when including both the WorkOrder and
ResponseInvoice tables.

Regarding JobNumbers etc. Every contract we receive is given a JobNumber. I
have another table that stores the jobs. JobNumber is a foreign key in
WorkOrder, and a foreign key in ResponseInvoice. Every JobNumber can have
many WorkOrders, every JobNumber can have many ResponseInvoices.
ResponseInvoice is not related to WorkOrder because an invoice can be created
as a seperate entity (ie, an invoice with no related WorkOrder). For
accounting purposes, every invoice must have it's associated JobNumber
included (as does every purchase order, timesheet etc etc etc).

Anway, thanks for all your help.

Dave

"Ken Sheridan" wrote:

The invoices seem to reference JobNumber which is a non-key column in the
work orders table, which implies this. It also points to a flaw in the
logical model, I think.

Ken Sheridan
Stafford, England

"Dale Fye" wrote:

It didn't occur to me that there might be more than one invoice for each WO.

--
Email address is not valid.
Please reply to newsgroup only.


"Ken Sheridan" wrote:

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave

Thank you, works perfectly.

"Ken Sheridan" wrote:

Dave:

Mea culpa; the asterisk should be within parentheses in the first subquery:

SELECT COUNT(*)

but not in the second:

SELECT *

Ken Sheridan
Stafford, England

"David M C" wrote:

Ken, I've tried your solution but get a syntax error in the two subqueries
between:

(SELECT COUNT *

and

WO2.JobNumber))

I'll try and figure it out.

Dale, yours almost works, but I get OrdersRaised and Invoiced counted twice.
I had this problem previously when including both the WorkOrder and
ResponseInvoice tables.

Regarding JobNumbers etc. Every contract we receive is given a JobNumber. I
have another table that stores the jobs. JobNumber is a foreign key in
WorkOrder, and a foreign key in ResponseInvoice. Every JobNumber can have
many WorkOrders, every JobNumber can have many ResponseInvoices.
ResponseInvoice is not related to WorkOrder because an invoice can be created
as a seperate entity (ie, an invoice with no related WorkOrder). For
accounting purposes, every invoice must have it's associated JobNumber
included (as does every purchase order, timesheet etc etc etc).

Anway, thanks for all your help.

Dave

"Ken Sheridan" wrote:

The invoices seem to reference JobNumber which is a non-key column in the
work orders table, which implies this. It also points to a flaw in the
logical model, I think.

Ken Sheridan
Stafford, England

"Dale Fye" wrote:

It didn't occur to me that there might be more than one invoice for each WO.

--
Email address is not valid.
Please reply to newsgroup only.


"Ken Sheridan" wrote:

Dave:

It should be possible to compute the second and last columns by summing the
return value of an expression which evaluates to 0 or 1, the third by means
of a subquery containing a further subquery. Try this:

SELECT
JobNumber,
COUNT(*) As TotalOrders,
SUM(IIF(CompletionDate Deadline,1,0)) AS WithinTarget,
(SELECT COUNT *
FROM WorkOrder AS WO2
WHERE WO2.JobNumber = WO1.JobNumber
AND EXISTS
(SELECT *
FROM ResponseInvoice
WHERE ResponseInvoice.JobNumber = WO2.JobNumber))
AS Invoiced,
SUM(IIF(Status = 5,1,0)) AS AwaitingInvoice
FROM WorkOrder AS WO1
GROUP BY JobNumber;

Ken Sheridan
Stafford, England

"David M C" wrote:

I have two tables:

WorkOrder:

WorkOrderID (Primary Key)
JobNumber
OrderDate
Deadline
CompletionDate
Status

ResponseInvoice:

ResponseInvoiceID (Primary Key Autonumber)
JobNumber
InvoiceNumber
InvoiceDate

In a query, I need to see (on a per Job Number basis):

Number of Orders Raised (easy, just a Count on the ID column)
Number of Orders Completed Within Target ( Count of Orders whose
CompletionDate Deadline)
Number Of Orders Invoiced
Number Of Orders Awaiting Invoice (Status = 5)

I can do the first one, but I can't combine a Count with anything in a
criteria field. Any help would be much appreciated.

Thanks,

Dave