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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote