I think I understand this time.
SELECT SUM(IIF(close_date is null AND drug_date is not null, 1,0)) AS CountOfOpen,
SUM(IIF(close_date is not null AND drug_date < close_date,1,0)) AS CountOfClosed
FROM MyTable
HAVING drug_date BETWEEN #1/1/2013# AND #12/31/2013#
- Bill
---In MS_Access_Professionals@yahoogroups.com, <Sheryl.Jones@state.mn.us> wrote :
It didn't work and probably because I didn't explain well enough what I need.
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <Sheryl.Jones@...> wrote :
I need to count the drug screens of both the open cases as well as
any closed cases that had drug screens requested between 1/1/13-12/31/13.
I need to be able to weed out the screen dates of the closed cases that were auto-generated (any that appear in the system after the date closed for that specific case) so they don't end up in my end total.
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
Is this what you are looking for?
SELECT COUNT(*) AS CountOfDates
FROM MyTable
WHERE date_closed IS NULL
HAVING drug_date BETWEEN #1/1/2013# AND #12/31/2013#
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
---In MS_Access_Professionals@yahoogroups.com, <Sheryl.Jones@...> wrote :
Good Morning.
I am trying to figure out how many drug screens [drug_date] were requested between 1/1/13 and 12/31/13. That in itself is easy enough for me to do:) However, some of the cases are now closed [date_closed].
Unfortunately (for me), the system auto generates screening dates in advance so I do not want to count all of the screens for the persons whose cases have already been closed & I cant figure out a way to set up the query to gather the data I need leaving out the screens of just the dates of the closed people post their closed date. Make sense?
I've tried several ways and get several answers- maybe just over-thinking, but don't want my data to be wrong, so here I am asking for some help setting up the parameters.
__._,_.___
Posted by: wrmosca@comcast.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Yahoo Groups
New feature! Create Photo Albums in Groups Effortlessly
Now, whenever you share photos with your group, a new album is automatically created in the Group. It's so simple! Try it now!
.
__,_._,___
Tidak ada komentar:
Posting Komentar