Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's a new one from the bureau of stupid government statistics. I have a social service agency who not only needs to report on when the first meeting with a client happened, but also when the second meeting happened and have that information available on an ongoing basis including a report that breaks them down within their date of arrival in Canada in several date ranges (within first year of arrival, within year 2 or 3, within year 4 or 5, after year 5).

    I'm thinking the easiest way to do this given the extremely complicated queries I already have to deal with is to have a storage table that I update whenever I run the associated reports rather than calculating this on the fly. I've noticed when there are a lot of records (about 10,000 clients and 45,000 meetings) that performance is slowing down. I've had to go to a new design for all of my queries because a new client needs to filter for different service sites as well as two different funders who serve different categories of immigrants.

    Any suggestions about most efficient way of the mechanics of finding the second meeting and recording it? Hopefully this is clear enough information.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    One option is to create a Top 2 query listing the first two dates, then create a new query based on the Top 2 query that retrieves the max (i.e. the later) of the two dates.

    Another option is to use something like

    DMin("DateField", "NameOfTableOrQuery", "DateField>#" & DMin("DateField", "NameOfTableOrQuery") & "#")

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='771391' date='20-Apr-2009 12:59']One option is to create a Top 2 query listing the first two dates, then create a new query based on the Top 2 query that retrieves the max (i.e. the later) of the two dates.

    Another option is to use something like

    DMin("DateField", "NameOfTableOrQuery", "DateField>#" & DMin("DateField", "NameOfTableOrQuery") & "#")[/quote]


    Thanks Hans. That looks pretty straightforward. I'll post back if I have problems. With this number of records, would I be correct in assuming that DMin will run slower than the Top 2 query?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DMin will probably be slower, indeed.

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

    Question

    This thread has returned to haunt me. It turns out I do not need the second meeting regardless of when it happens. What I really need is the first meeting which happens at any time subsequent to the month the first meeting happened in. Consider 2nd meeting as more of a philosophical and bureaucratic construct than a reality.

    For example: If I come in as a client on the 10th of June and come back again on the 20th of June and then the next time I come in is October 3, I do not want that meeting on the 20th June as the "2nd meeting" I want the meeting in October as the "2nd meeting". (In the month of June, I am "New" for the whole month regardless of the number of visits.) While I'm at it, when I come in again on the Nov 5, I am now "returning" and when I come in on Apr 12, 2010 I will be "Previous" because the fiscal year starts on Apr 1.

    All of these are premised on counting someone only once within any month they visit. All of these calculations are mutually exclusive, so I am imagining a column in a query that references whether they are "New" or "Ret" (a different kind of returning!) in a given month. If they are new, I have one kind of calculation that is already working. What I need is to evaluate if they are "Ret" and then decide if they are 2nd, Returning or Previous based on the information above.

    You gotta hand it to the Ontario government. Clearly there are some civil servants who have way too much time on their hands to think up this kind of nonsense for an underfunded program!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can do this using a series of queries, each depending on the previous one. For large numbers of records, it will be rather slow, so you may want to reserve fields in the table for this, and use update queries to store the values in those fields.

    See the attached small sample database.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779981' date='15-Jun-2009 18:31']You can do this using a series of queries, each depending on the previous one. For large numbers of records, it will be rather slow, so you may want to reserve fields in the table for this, and use update queries to store the values in those fields.

    See the attached small sample database.[/quote]
    Thanks Hans. This looks like exactly what I need. I will post back if I have any questions.

Posting Permissions

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