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!
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!
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
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
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.