Needs some guidance

[cdoyle]

Working on a database that tracks codes, descriptions etc.

One of the items they would like is a 'change' report.

On this report, they would like it too list 2 things. They would like it to list if any entries that refer to a expired edit type (determined by the end date field).
Then if the code was updated to a new edit type it would display this too.

So the report would look something like this.

If the code that refers to a expired edit type, and has no update it would say
code# removed <then list the edit type that expired>

or

If a new entry for that code was created with a new edit type, it would say something like

Code# Changed from <old edit type> to <new edit type>

Is this possible?
I'm not even sure how to visualize it, so hard time moving forward with it.

 

[cdoyle]

anyone have any ideas or thoughts?

I know how to find the ones that ended, and find any that started.
The users select a date, and all entries have a 'set' start date. So I have a query that looks for any entries with an end date -1 day, to find any that ended the day before.

I just can't figure out how to get all this into a report, where it looks like how I have above.

 

[The_Doc_Man]

OK, let me try to clarify this for you.

There are a couple of issues implied in your questions. I'm going to GUESS (and admit it is such) how you might approach this.

You've got entries that relate to some sort of codes. The codes have expiration dates. The entries either do not or the dates can differ from the code dates. Presumably there is some field in the entry table that links you to the codes. PROBABLY the code number but again, I am assuming something. Probably a many-to-one mapping of entries to codes.

Consider this: Add two Yes/No fields to your table of entries. The first of these is your "I've expired due to my edit code reference" field. Write a query that links the entries in your main table to the edit-code table. This gives you a recordset that contains both the date on the edit field and the records that depend on that field.

OK, you can write an UPDATE query that resets this marker. Then write a second query that SETS the marker based on whether the expiration date < Now(). (I.e. expiration date is in the past.) You can write this one from a query grid.

OK, now go back and make a query that selects marked items. For every marked item, make an entry in a history table that copies the edit code and the entry's prime key and the date and maybe a code letter that says, in effect, On this date, record X using code Y expired. Remember I said "TWO Yes / No fields?" The second one is the semi-permanent flag that says "RIGHT NOW this field is expired." If you are about to log an expired field that was ALREADY expired, don't make a log entry.

Second query: If you are about to update a code entry, scan through the primary table for records matching that code entry. Mark them.

In a follow-up query, update all marked records to the new edit code AND reset their "I'me Expired long-term" flag AND make an entry in the history file.

Then drive all reports from the history file. You can also choose to purge the history table when a record that was expired becomes unexpired 'cause you finally updated the edit code table.

The key is "Divide and conquer." Use a query and the "short-term action" flag to identify records. THEN make history entries, mark the "long-term status" flag as needed, and update what needs updating. You can do this in a series of queries if you need to.

Odds are that the "I've expired" event can be done in one swell foop because it only depends on Date1 < Date2 type decisions. The "I've been reborn" even probably can only be done one edit code at a time. But if you methodically just break it down into its components, you can attack it pretty easily.

If your report only needs to know the last history entry for anything, purge the table before generating the report. This might be a bit more complex, but heck, not impossible.

Think about this for a while and see if anything percolates. Come back later with further questions if you still unsure.

 

[cdoyle]

Hi,
Thanks for replying and giving me some suggestions.

I'm going to try and describe what I have been working on, and hopefully what you suggested will still work.

I have a 'codes' table, this table lists all the codes, code description and any other info related to the code itself

We also have a 'edit table' This table lists all the different edits, edit description ect.
We also have columns for effective dates of the edit itself. Start/End.

(I have created a query, that will detect if any edits have expired)

Then we have our main table, here is where the users can select a code, and assign an edit (PK from the edit table)too it. They also add other info, but not needed for this.

Updates are made each month, all new entries will have the same effective start date, and any entries that end will have the same effective end date.

When the user runs the report, they will know what the new effective start date is already. When the report is ran, it will prompt them for a start date and will also prompt them for the edit type to search (so it will only display the entries with the edit type selected).

I hope I described what we have a little better.

Now I'm wondering if we still need the checkbox for the 'I've expired due to my edit code reference' since I have a query that can pull any codes associated with expired edit types?