Help with query count (1 Viewer)

ielamrani

Registered User.
Local time
Yesterday, 21:41
Joined
May 6, 2008
Messages
51
Hi,

This query does everything I need except one thing and I am reaching out to expert out there for help. Thanks in advance.

SELECT DISTINCT Data.date_prescribed, (SELECT Count(*) FROM [Data] WHERE [Data].edi="T" OR [Data].printed="3") AS TotalPres,

(SELECT Count(*) FROM [Data] WHERE (([Data].edi="T" OR [Data].printed="3") AND [Controlled Substance] = "No")) AS TotalNonContPresc,

(SELECT Count(*) FROM [Data] WHERE [Data].printed="3") AS [Total Printed] INTO Report

FROM Data;

Result

Query2
date_prescribed TotalPres TotalNonCont PrescTotal Printed
1/13/2016 420 370 16
1/14/2016 420 370 16
1/15/2016 420 370 16
1/16/2016 420 370 16
1/17/2016 420 370 16

the total is the same of all days. how can I fix this?
 

plog

Banishment Pending
Local time
Yesterday, 20:41
Joined
May 11, 2011
Messages
11,000
I don't know what to tell you, its doing exactly what you are telling it to do. Check out this subquery:

Code:
SELECT Count(*) FROM [Data] WHERE [Data].edi="T" OR [Data].printed="3"

It has no criteria on it that uses the date, so its returning everything. So 1/13/2016 gets the same result as 1/14/2016 as 1/15/2016 etc.

I think it could be made more simpler without subqueries. You would move the subquery criteria to a field in the main query like so:

Code:
SELECT date_prescribed, SUM(Iif(edi="T" OR printed="3", 1,0) AS TotalPres
FROM Data
GROUP BY date_prescribed

For each subquery you do that--make it a field in the main query.

Also, why are you saving this into Report? Calculated values shouldn't be stored---they should be calculated. Use this as a SELECT query and then reference it when you need any of the data in it.
 

ielamrani

Registered User.
Local time
Yesterday, 21:41
Joined
May 6, 2008
Messages
51
Worked. very simple. have to admit that I made the query very complicated.

I am going to modify the query to give all the results.

Thank you sooo much.
 

Users who are viewing this thread

Top Bottom