Filtering Database by Active Records

[twinnyfo]

Friends,

In ongoing efforts to make sure my own databases are in line with good design principles, I am converting two sets of tables back into one.

Historically, when we had groups of data we no longer referred to on a regular basis, I would identify all those records, copy them into an "Archive Table" and then delete those records from the master tables.

After discussions with some of the experts here, it has been determined that the proper design is to have a flag in the master table indicating an active or inactive record. I don't think it will be much of a problem to re-import all the old records into the active tables. This will just take a little finagling to make sure my forms, queries ad reports are looking at things properly.

Here is the issue: our data is mnaaged in various groups (which I will describe in greater detail below). There are multiple ways of indicating an active record, some of which I think are more wasteful than others, but also which may have certain advantages.

Here is the set up of my tables, and hopefully, I will be able to properly explain my dilemma.

tblProjects (A list of all the Projects we work on):

Field        Type/Notes
=====        ==========
ProjectKey   PK, Long, Autonumber
ProjectID    Text, Short Name of Project
ProjectLong  Text, Long Title of the Project
Others       ...

tblBoardIDs (A list of Board IDs assigned to each Project):

Field       Type/Notes
=====       ==========
BoardIDKey  PK, Long, Autonumber
ProjectID   FK, refers to tblProjects
BoardID     Text, Short descriptor of Board ID

tblEligibles (a List of Names, who are eligible for a Promotion Board):

Field        Type/Notes
=====        ==========
EligibleKey  PK, Long, Autonumber
BoardID      FK, refers to tblBoardIDs
Others       ...

So, here is my dilemma: When we work on records, we typically do it "by Project", so we just select a Project, which will include all the Board IDs assigned to that Project, and that also includes all Eligibles in each Board ID.

I can easily add a field to tblProjects, indicating that it is no longer an active project. Then, when we want to choose a project, we only select those Projects without that flag. Easy breezy!

However, because of the old design, there are many times that we need to look at "all active eligibles". Instead of using any type of query, which could cause some problems with making updates, my forms use tblEligibles as the Record Source.

Here is my question: Is there a way to filter tblEligibles without using a Query joining tblProjects and tblBoardIDs (which is the normal way for me to select all eligibles assigned to a project)?

The other option would be to identify all records in tblEligibles which are associated with inactive projects and have a separate flag for each record--which makes it easier to filter the Table, but seems redundant if all I really need is to identify the inactive Projects.

I can only imagine that some of you experts have run across this situation at least once in your experience. Before I start demolition on the old structure, I want to make sure I have a well-set plan ahead.

I'd appreciate any thoughts and advice you may have.

[jforbes]

Sounds like a fun project.

I’ve got some questions for you:
1) What is the platform for the Backend database? SQL Server, MS-Access?

2) If you were to query tblEligibles to determine if they should be archived, what criteria would you use? Just being associated with an Inactive tblProject record? Or can a tblEligibles record be associated with multiple tblProjects both Active and Inactive?

[twinnyfo]

Hey J,

Thanks for the response. This is all done in MS Access 2010 FE and BE.

The criteria for determining an "inactive project" are several, but are also determined using other tables.

1) First, we consider a project "done" when we have completed all Tasks associated with the Project. These tasks are in a related table (tblTasks), and as user complete the tasks associated project, when there are no longer any tasks to complete, the project itself is complete.

2) One of the crteria for "completeness," is that the results of the promotion board have been formally released. This release occurs on a specific date, and that date is stored in tblProjects.

3) Because we are always asked to perform some data analysis on the promotion results, we allow ourselves 60 days after the promotion release date for the records to remain active.

When all three above criteria have been met, we then archive the entire project (the Project ID gets archived, the Board IDs get archived and the Eligibles get archived).

This is all plain and simple, and currently functions flawlessly--the new change that I will make is that when those three criteria are met, instead of "archiving" the data, I will (hopefully) merely change the flag on the record of tblProjects to "Archived".

So, again, my challenge is not in identifying which records to archive, but in working with a table (without an archive flag in it) to list only non-archived records.

I also recognize that the solution may be to add that archive flag to tblEligibles, which, again, to me, seems redundant. If I can identify which project is archived, I am able to identify all Eligibles who are archived. I just don't know how to do that without a separate query (which I realize, may also be the best solution).

Just wondering if there is a way to add a WHERE clause to a filter string that would weed out archived records, based on the flag in a different table....

BTW, an eligible may be associated with multiple projects, but in the few rare instances in which they are, we have a separate record because just about every other field in that table will also be unique.

[zmbd]

IDK, might be usefull in your situation:

I use a relationship like this

[tbl_recordstatus]
[recordstatus_pk]
[recordstatus_description]

[..._pk] 1 thur 3
[... _description] legacy, inactive, active

[tbl_recordreporting]
[recordreporting_pk]
[recordreporting_description]

[..._pk] 1 thur 3
[... _description] Redaction, NonReporting, Reporting

So, now in the inventory there are FKs to these two tables.

TF I'm sure you can see now what I'm doing...
So an item that is 'purged' would have a inactive, nonreporting.

There are some items that are in the inventory; however, I don't need them showng up in the reports so active, nonreporting

Then there are things that should never be placed in hard copy and these are marked redacted, an inactive, redacted may show up on a screen, but in the report conditional formating makes sure the record is blank
etc...

I also have historical events that I tag FK to the [tbl_recordreporting], I could have used a true/false however after reading this Why I stopped using Yes/No fields
- Allen Browne several years ago I went with this route. This also avoided an issue that with null values in boolean fields between Access and the SQL server.

[twinnyfo]

Z,

Although I understand where you are going with your post, it still misses answering my original question.

Even with using such a set up as yours (which is beyond the scope of what I need), if I were to apply those settings to tblProjects, how is it possible to filter a form using tblEligibles as a record source, without creating a separate query as a record source?

I understand that I could include two additional fields in tblEligibles, but I am trying to avoid this, as what applies to one record in tblProjects also applies to every related record in tblBoardIDs and tblEligibles. Remember, I am trying to avoid redundancy, which is supposed to be a cardinal rule of relational databases, right?

[zmbd]

TF:
Sorry cross posted with J (^_^)

2).... This release occurs on a specific date, and that date is stored in tblProjects.

3) Because we are always asked to perform some data analysis on the promotion results, we allow ourselves 60 days after the promotion release date for the records to remain active

Unless Item 2 and 3 can occur prior to Item 1 being complete, then your active status on the project and related records is based on the ageing of the PRD. ?

So
>return Projects less than 60 days past PRD.

>> This will allow you to return your boards associated with the active projects

>>> This will allow you return your Eleigables associated with the active projects

IRC: Select queries are not considered as part of the normalization/non-redundancy of data criteria as they do not re-enter/store the same base data. Thus, creating a new query shouldn't be an issue other that manpower.

[twinnyfo]

Friends,

1. Please, stop answering questions about how I can and should identify the archivable records. There are no problems with identifying these and have not been for the seven years I have been doing it. Plus, it has nothing to do with my question.

2. @Z, concerning your final statement, yes, I know that I can create a SELECT query that will suit my needs. I am fully capable of doing this and I know exactly how it will look--I've done it and it works. If the only solution is to create a query to do this, then I am willing to do that.

3. My question is simply, is it possible to use the table tblEligibles as the record source for a form, yet somehow filter that form based on criteria in the Table tblProjects? That is, for all projects that do not have the "Archived" flag (however that flag is identified, whenever that flag is identified, based on whatever criteria I choose) only those related records in tblEligibles will show on the Form.

Perhaps I have been unclear in my question.

[Rabbit]

One way to filter the eligibles would be to do something like this:

WHERE BoardID IN (
   SELECT BoardIDKey
   FROM tblProjects
   INNER JOIN tblBoardIDs
   ON ProjectKey = ProjectID
   WHERE Active
)

[twinnyfo]

Rabbit,

BINGO! I guess, now my question would be, how would one have known that in the first place so that one could have figured it out for oneself???

Very minor mod:

WHERE BoardID IN (
    SELECT BoardIDKey
    FROM tblProjects
    INNER JOIN tblBoardIDs
    ON tblProjects.ProjectKey = tblBoardIDs.ProjectID
    WHERE not Archived
)

For some reason, it breaks if I don't explicitly declare the table names...

Thanks for everyone's help and discussion!

Off to the (re)construction site.....

[zmbd]

TF, relax...

Here is my question: Is there a way to filter tblEligibles without using a Query joining tblProjects and tblBoardIDs(which is the normal way for me to select all eligibles assigned to a project)?

I personally see no way to use the tblEligibles directly unless there is additional infromation about this table that you have nor provided.

The answers given were in an attempt to provide some guidence either via additional fields or using the table schema you've provided.

Sorry if this irritated you.

[zmbd]

Line 4

Still a join

which doesn't answer your question as give in the original post.

Line 6
You did not provide that information in the original post.

[twinnyfo]

Z,

What Rabbit provided was a string that can be set as a filter for the Form, which works perfectly--exactly as requested in the original question. I can now use tblEligibles as the Record Source and use a filter (instead of using a query for the record source).

This also allows for non-redundancy, as all I need is one additional field in one table which will affect hundreds of records. This was my goal.

The minor mods are not that Rabbit "mistakenly" used "Active" instead of "Archived". My correction was to add the table names in the join, which, when absent, did not allow the filter to work properly, explained directly after my block of code (which could potentially help another user who runs across the same problem).

My apologies for getting irritated earlier, but I thought my question was pretty straightforward, but all the responses seemed to give direction that was not needed, nor asked for. Typically, I appreciate all guidance on this forum--including yours!

I am the first to admit many weaknesses in my db skills, even though my co-workers are amazed. However, they do not see the expertise that I see on this forum, which is why I sought my solution here, first.

Please forgive any inappropriate responses I may have made.

[zmbd]

TF, if you will, when you wrote this: " filter tblEligibles " my mindset went to the table level, and using that as the recordsource. For some reason, I never caught on to the form level and the filtering at the form.

Now mind you my post at #4 cross posted with both J's at #2 and yours at #3; thus, my reply was still based only on what I thought I had read in #1.

Still after re-reading the thread to pickup #3's information:

Just wondering if there is a way to add a WHERE clause to a filter string that would weed out archived records, based on the flag in a different table....

it still didn't trip in my mind that you were at the form level and not the table level.... with 20/20 vision, if you had hit me on the head with "z... can we do this using the form's filter..." I may not have had the answer, but it would have caught my attention.

Of course, Rabbit picked up on it right away; however, that doesn't surprise me at all.. he's got the mind for it!

I'm pondering Rabbit's SQL though... going back to the concept in post#6. Couldn't we alter the subquery's where clause so that WHERE Active is instead based on the aging of the PRD? Then you shouldn't need an additional field if I am following this train on the right track now.

In the future when you mention "filtering" don't be surprised if I ask if you want this in the form or table/query - I try not to irritate my neighbors the same way twice (^_^)