Re: [MS_AccessPros] Re: Query Help- Drug Screening

 

Yea! Glad I could help.

Regards,

Bill Mosca

---

From: "Sheryl.Jones@state.mn.us [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, August 6, 2014 11:01:31 AM
Subject: [MS_AccessPros] Re: Query Help- Drug Screening

 

THANKS!

I got it.

Sheryl

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

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@...> wrote :

It didn't work and probably because I didn't explain well enough what I need.

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 (8)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___