Query with counts for Chart in report

[PeterDuthie]

Hello Everyone. I need to get back two values from a query - 1. the total number of rows in a table and 2. the number of "incidents" which have been "signed off" (i.e.) dealt with successfully within 48 hrs. The two values are intended for a pie chart. Here's what I've got:

SELECT Count([T-incidents].[Signed off]) AS [Closed within 48 hrs]
FROM [T-incidents]
WHERE ([T-incidents].[Date of incident] + 2) >= [T-incidents].[Signed off];

How can I get the query to return the total number of incidents.

Thanks again to everyone who reads this.

(I'm cross posting this to the "Reports" forum - If I'm not supposed to do this please let me know)

Cheers,
Peter

 

[Psychpt]

Try:

SELECT Count(*) AS Total, (SELECT Count([T-incidents].[Signed off]) AS [Closed within 48 hrs]
FROM [T-incidents]
WHERE ([T-incidents].[Date of incident] + 2) >= [T-incidents].[Signed off]) FROM [T-incidents];

HTH

PsychPt@Hotmail.com

 

[PeterDuthie]

Very good Psychpt - this works perfectly. I see that DCount helps here too.

Cheers,
Peter

 

[Psychpt]

you're welcome.
I try to stay away from the Dfunctions, they say it's not as efficient.

 

[PeterDuthie]

Hello again Psychpt. You're obviously way ahead of me on SQL. Do you have a book recommendation? I have the excellent Access 2000 Developer's Handbook set but a more detailed discussion (with worked examples?) would be really useful to me.

Cheers,
Peter

 

[Psychpt]

Sorry, I don't have any recommendations maybe someone else here might be able to help you with that.