通过Active Records过滤数据库 [英] Filtering Database by Active Records
问题描述
朋友，
为了确保我自己的数据库符合良好的设计原则，我正在将两组表格转换为一组。
从历史上看，当我们不再定期提及数据组时，我会识别所有这些记录，将它们复制到存档表中。然后从主表中删除这些记录。
在与这里的一些专家讨论之后，已经确定正确的设计是在主表中有一个标志，表示活动或非活动记录。我不认为将所有旧记录重新导入活动表会有很大问题。这只需要一点点，以确保我的表单，查询广告报告正确地查看事情。
这是问题：我们的数据被分成不同的组（我会在下面更详细地描述）。有多种方式表示活动记录，其中一些我认为比其他记录更浪费，但也可能有一些优势。
这是我的表的设置，希望，我能够正确解释我的困境。
tblProjects （我们工作的所有项目清单）：
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):
推荐答案
听起来像一个有趣的项目。
我有一些问题要问你：
1）什么是平台f还是后端数据库？ SQL Server，MS-Access？
2）如果您要查询tblEligibles以确定它们是否应归档，您会使用什么标准？只是与非活动tblProject记录相关联？或者tblEligibles记录是否可以与多个活动和非活动的tblProject相关联？
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?
嘿J，
感谢您的回复。这些都是在MS Access 2010 FE和BE中完成的。
确定非活动项目的标准。有几个，但也是使用其他表确定的。
1）首先，我们考虑一个项目完成当我们完成与项目相关的所有任务时。这些任务在相关表（tblTasks）中，当用户完成任务相关项目时，当不再完成任务时，项目本身就完成了。
2） 完整性的标志之一，是促销委员会的结果已正式公布。此版本发生在特定日期，该日期存储在tblProjects中。
3）因为我们总是被要求对促销结果进行一些数据分析，所以我们允许自己60天在促销发布日期之后，记录保持活动状态。
当满足上述所有三个标准后，我们将归档整个项目（项目ID被归档，董事会ID获得存档和符合条件的存档）。
这一切都很简单，目前功能完美无缺 - 我将做的新变化是，当满足这三个标准时， 存档数据，我希望（希望）只是将tblProjects记录上的标志更改为Archived。
所以，我的挑战不是要确定要归档的记录，但是在使用表（没有存档标志）时只列出非存档记录。
我也认识到解决方案 可能 将该存档标志添加到tblEligibles，这对我来说似乎是多余的。如果我可以识别哪个项目已归档，我就能够识别所有已归档的合格人员。我只是不知道如何在没有单独查询的情况下做到这一点（我意识到，这也可能是最好的解决方案）。
只是想知道是否有办法添加一个根据 不同的 表中的标志，过滤掉字符串的过滤字符串的WHERE子句....
顺便说一下，符合条件的 可能 与多个项目相关联，但在极少数情况下，我们有一个单独的记录，因为几乎所有其他字段都是table也是唯一的。
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.
IDK，在你的情况下可能会有用：
我使用这样的关系
[tbl_recordstatus]
[recordstatus_pk]
[recordstatus_description]
[... _ pk] 1 thur 3
[... _description]遗产，不活跃，活跃
[tbl_recordreporting]
[recordreporting_pk]
[recordreporting_description]
[... _ pk] 1 thur 3
[... _description]编辑，非报告，报告
因此，现在库存中有两个表格的FK。
TF我相信你现在可以看到我正在做什么......
因此，一个被清除的物品会有一个不活动的，不报告的。
库存中有一些物品;但是，我不需要它们在报告中如此活跃，不报告
然后有些东西永远不应放在硬拷贝中，这些都被标记为编辑，不活动，编辑可能会显示在屏幕上，但在报告中有条件的格式化确保记录为空白
等...
我也有历史事件虽然我将FK标记为[tbl_recordreporting]，但我可以使用true / false但是在阅读此之后为什么我几年前停止使用是/否字段
- Allen Browne 我选择了这条路线。这也避免了在Access和SQL服务器之间的布尔字段中使用空值的问题。
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.
这篇关于通过Active Records过滤数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
