Results 1 to 12 of 12
  1. #1
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6

    Query for count in location with change in location mid-month

    I need to identify in which programs(locations) the Medicine was given.
    These are tables/fields I receive from our records system.
    My only common field is the TieName and IndividualName but that is a many to many relationship.

    tblProgramsbyIndividuals
    ID (Key)
    LastName
    FirstName
    MiddleName
    IDNumber
    IndStatus
    Program(locations)
    ProgramType
    ProgramName
    EnrollmentDate
    DischargeDate
    TieName
    tblMARPasses
    ID (Key)
    IndividualName
    AdministeredDate
    AdministeredTime

    Knowing that there are only 2 "ProgramType":


    1. Day (Usually occurs Mon-Fri 8a-3p)
    2. Residential (all others)

    I was able to identify the Program if there is only 1 Day or 1 Residential (or 1 of each) for each individual. My issue is we move people a lot. Using the Enrollment and Discharge Dates, I want to identify when someone moves from 1 Residential home to another sometime during the month...say the 15th.

    Can anyone direct me to some examples of querying Data using Dates that is beyond the usual "Between #Date# and #Date#". I really think this is more complicated then what comes up in a general Google search.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And do you have a table with records that show when client is moved?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    It's in the table: 'tblProgramsbyIndividuals'. When a client is moved their last day of old address is entered in Discharge Date. And a new record is created in that same table with the new program and the 1st day at the new place is the Enrollment date. I make sure it is always the day after the Discharge Date. So there should be no overlapping dates.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    beyond the usual "Between #Date# and #Date#".
    given you have enrolment and discharge dates, why does it need anything beyond that?

    Code:
    SELECT *
    FROM Individuals INNER JOIN Passes ON Individuals.TieName=Passes.IndividualName
    WHERE administereddate+administeredtime Between enrollmentDate and nz(dischargeDate,Now())

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    want to identify when someone moves from 1 Residential home to another sometime during the month...say the 15th.
    missed this bit

    Code:
    SELECT *
    FROM Individuals INNER JOIN Passes ON Individuals.TieName=Passes.IndividualName
    WHERE administereddate+administeredtime Between enrollmentDate and nz(dischargeDate,Now()) AND format(enrollmentDate,"yyyymm")=format(dischargedate,"yyyymm")

  6. #6
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Ultimate goal is to count how many Passes are in each program by month. The Total from this query should not be more than the total # of records in the Passes table. By using your code I get way too many records. I'm working on stripping confidential info so I can provide a copy.

  7. #7
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    Here is my database.
    Attached Files Attached Files

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so which is the query you are trying to get right?

  9. #9
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    I don't have one. I was trying to create one. I guess we can start with qryRESPassesbyProgramWithINDName. This shows all passes of medication by residential location and by the individual's name. But the total is wrong because Jane Johnson moved in the middle of the month. So the query is double counting her total instead of seperating them into the respective locations. How can I use the Enrollment and Discharge Dates to count the Passes for each location?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I’ll so from the example data provided show the result you expect. Just saying it is wrong and double counting still leaves me guessing what you mean

  11. #11
    elkay73 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    6
    I'm guessing that I just can't explain this properly. I'll look for help somewhere else. Thank you for your time.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there is a saying 'if you can't work it out on paper, it can't be done'. But good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Location change
    By Srin in forum Access
    Replies: 5
    Last Post: 12-22-2020, 12:19 AM
  2. change file location
    By Mbakker71 in forum Import/Export Data
    Replies: 4
    Last Post: 01-29-2014, 08:17 AM
  3. Change Primary Location of Access Database
    By MichaelWR90 in forum Access
    Replies: 8
    Last Post: 06-03-2013, 09:45 AM
  4. Attachment file location change
    By gary742 in forum Access
    Replies: 3
    Last Post: 03-22-2011, 12:26 PM
  5. Replies: 0
    Last Post: 03-01-2011, 10:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums