Sheryl-
What is your table name, and what are the fields in the table? You basically need to do:
((Drug_Date BETWEEN #1/1/2013# and #12/31/2013#) AND NOT EXISTS ( … A subquery that finds any "closed" record for the current patient between those dates .. ))
OR
(Drug_Date Between #1/1/2013# AND (SELECT … find the date it was closed …)
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Aug 6, 2014, at 12:14 PM, Sheryl.Jones@state.mn.us [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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
Microsoft Office Access MVP
My nothing-to-do-with-Access blog
---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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
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