Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    choose correct result based on date (2000(SP3))

    I have a series of reports for a social services agency that track various statistics about their clients including their immigration status. This status can change over time as they progress toward citizenship and clients who belong to certain categories (like being a citizen) are filtered out in some reports.

    Immigration status is stored in a separate table from the Clients table with a one to many join on ClientID. There are 3 fields in this table, ClientID, ImmigrationStatus and StatusDate which is the date that status was achieved. Primary key is ClientID and Immigration Status.

    All works well until their status changes and there are now two records for one client in this table. In the setup queries for reports I need to retrieve only the status that is valid at the date of a given meeting with staff. Otherwise, the client ends up in the query the number of times that they are in the Status table. These reports and queries can include any range of dates but all stats are calulated in periods of one month. If more than one month is in a report, there are separate lines for each month's stats. Meetings are recorded in a meetings table. Here is a scenario:
    Client A comes in 4 times over a period of 2 months and in that time her status changes from X to Y
    MeetingDate ImmStatus StatusDate
    Meeting 1 Jan 2 X Dec1
    Meeting 2 Jan 15 X Dec1
    Meeting 3 Feb 4 X Dec1
    Meeting 4 Feb 20 Y Feb15

    Reports are made on a monthly basis.The status at the end of the month is the one I want so the results for this scenario should be Status X in January and Status Y in February. I not only need to report on this criterion, I need to filter on it. Some of my reports count people and some count the actual number of visits. A people report says this person was an active client in January and February . A visit count says they came in twice in each month.

    I'm thinking some sort of Domain Aggregate function might work or a custom function but I just can't quite wrap my head around this one. <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: choose correct result based on date (2000(SP3))

    You can create a series of queries:
    - First a query to calculate the month of the meeting date, and to select status dates in or before the month of the meeting date.
    - Next, a totals query based on the first query that selects the most recent status date for each client and month.
    - Finally, a query based on the totals query and the Status table to retrieve the active status for each client and month.

    See attached demo.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: choose correct result based on date (2000(SP3)

    I took a quick look at it and it seems straightforward. I will post back if I am having difficulties. My only concern is that it will add at least one more query to a string of them and slow down even more the generation of the reports. We'll see.

    Thanks as always Hans. I certainly would not have come up with that in 10 times the amount of time it took you to reply. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •