Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    14

    Unanswered: Report and multiple time periods

    I have a report that pulls all meeting notes for a specified manager and then displays the corresponding rank for that manager from another table. This works fine until the rank is changed (we want to keep track of all rank changes). It is currently printing all the notes for the current rank and then repeats the notes for the original rank. Is there a way for me to include only the most current rank on the report? Thanks in advance for your help!

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    It would be easier to offer a suggestion, akondra, if you gave more details about your structure.

    Does your structure look something like this:
    • a table of managers,
    • a table of rank advancements, (including manager, rank, and date of advancement), and
    • a table of meeting notes (including manager, and date of meeting)?
    Last edited by JTRockville; 10-28-04 at 09:30.

  3. #3
    Join Date
    Oct 2004
    Posts
    14

    Details

    Sorry - you are absolutely correct! At any rate, the structure is as follows:
    [*]An Org table storing unique org_id's for each manager[*]A Notes table that stores multiple notes for many org_ids, joined on org_id[*]Rank table storing ranks for each org over time for many org_id's, joined on org_id

    My report displays the as of date for the meeting note, the author, subject, and note itself. The record source is a query that prompts the user to enter a particular manager.

    I originally just had the rank as another field on the form forgetting about the one to many relationship. Now we finally have a mgr with a rank change so it's producing the same notes twice, one for each rank. I've tried inserting the rank portion as a subreport so it would just display in the header of each page, but I haven't been able to get it to just bring back the most current rank (I've sorted rank descending on as_of_date and tried to pull the latest record or max, but no luck so far). Does this make sense?

    I appreciate any suggestions you might have. Thanks!

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    This might not be the most elegant solution.... but here goes.

    In this example, I used used tblrank as the table name for the rankings, with org_id, rank, and as_of_date as the field names; and tblnotes as the table name for the notes, with org_id, note_date, and note_text as fields.

    • Make a query that returns the org_id, rank, and highest as_of_date from your rank table. Prompt as you did in your original query.

      SELECT tblrank.org_id, Max(tblrank.as_of_date) AS MaxOfas_of_date
      FROM tblrank
      GROUP BY tblrank.org_id
      HAVING (((tblrank.org_id)=[Enter org_id (or whatever your prompt is)]));

      Save it (I'll call this qrySelectedManager in the next query.) You can join the manager's table here and select any fields you may need for reporting (such as name, etc.).

    • Make a union query to get the range of dates for each rank. The most recent rank will have null as the through_date.

      SELECT tblrank.org_id, tblrank.rank, tblrank.as_of_date AS start_date, Min(EndRank.as_of_date) AS through_date
      FROM (qrySelectedManagers INNER JOIN tblrank ON qrySelectedManagers.org_id = tblrank.org_id) INNER JOIN tblrank AS EndRank ON tblrank.org_id = EndRank.org_id
      WHERE (((EndRank.as_of_date)>[tblrank].[as_of_date]))
      GROUP BY tblrank.org_id, tblrank.rank, tblrank.as_of_date
      UNION ALL SELECT tblrank.org_id, tblrank.rank, tblrank.as_of_date AS start_date, Null AS through_date
      FROM qrySelectedManagers INNER JOIN tblrank ON (qrySelectedManagers.MaxOfas_of_date = tblrank.as_of_date) AND (qrySelectedManagers.org_id = tblrank.org_id)
      GROUP BY tblrank.org_id, tblrank.rank, tblrank.as_of_date;

      Save it (I'll call this qrySelectedManagerRankRange in the next query).

    • Make a third query that uses qrySelectedManagerRankRange as it's source, inner joined to your notes table by org_id to see the notes by rank.

      SELECT qrySelectedManagerRankRange.org_id, qrySelectedManagerRankRange.rank, tblnotes.note_date, tblnotes.note_text
      FROM qrySelectedManagerRankRange INNER JOIN tblnotes ON qrySelectedManagerRankRange.org_id = tblnotes.org_id
      WHERE (((tblnotes.note_date)>=[qrySelectedManagerRankRange].[start_date]) And ([qrySelectedManagerRankRange].[through_date] Is Null OR [qrySelectedManagerRankRange].[through_date]<(tblnotes.note_date)));

      Save this query and use it as the recordsource for your report, modifying the table names and field names as needed.

  5. #5
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    .. oops, posted twice! ...

  6. #6
    Join Date
    Oct 2004
    Posts
    14

    Thanks!

    Thank you so much! Elegant or not, I'll be thrilled to have a working solution.

Posting Permissions

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