一次删除多个表中的选定记录 [英] Deleting selected records from multiple tables at one go
问题描述
嗨
我有这个数据库，可以计算并存储特定部门员工赚取的激励金额
。通过输入日期，班次（早上，晚上或晚上）和''员工
名称''输入每条记录
。还有另一张桌子可以为班次分配一张身份证，即早上和晚上为
1,2和3。夜班分别。从母亲
表中，每位员工的日期计算奖励按照他/她的班次来计算。总的来说，数据库填充在3个表中，
，''出勤''，出勤最终''和''奖励''，每个
有Date，Shift和&员工字段。
现在碰巧由于错误的数据输入，删除与特定日期有关的所有记录并且&
$ b所需的时间变为b
$ b从上表中的每一个转移。这更是如此，因为所有表格都有多个主键，并且通过追加查询形成了表格，我没有任何表格。其他选择，但
删除与该特定日期相关的所有记录+从每个表格中转移
。删除后，操作员将能够为该特定日期+班次重新输入新的
数据，查询将重新计算
并相应地形成表格。
我想知道如何解决这个问题。我不太熟悉
删除查询或VBA。
最初，我希望用户首先需要输入
日期
以及他希望从表格中每个
删除记录的班次。
它应该对于与上述3个表相关的3个删除操作，用户必须键入日期+转换三个
次。
键入后，用户需要在启动
删除操作之前再次重新确认。
删除操作将找出3个表中每个表中的记录
并相应删除。
删除后，需要显示一条消息''所有记录
for ..... date。 ..和... shift ..已被删除''。
如果用户键入组合（日期+班次），其记录为
表中不存在，则应显示一条消息帽子''没有
这样的记录存在''。
非常感谢你的帮助。
shriil
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the ''employee
name''. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In total, the database is populated in 3 tables,
namely, ''Attendance'', Attendance Final'' and ''Incentive'', each of
which
has the Date, Shift, & employee field.
Now it so happens that due to erroneous data entry, it becomes
necessary for deleting all records pertaining to a particular date &
shift from each of the above tables. This is more so because as all
the tables have multiple primary keys, and sone of the tables are
formed through ''Append'' queries, I do not have any other option but to
delete all records related to that particular date+shift from each of
the tables. After deletion, the operator will be able to reenter fresh
data for that particular date+shift and the queries will recalculate
and form tables accordingly.
I would like to know how to go about this. I am not quite conversant
with the delete queries or with VBA.
Initially, I would prefer that the user first needs to key in the
date
and the shift for which he wants the records to be deleted from each
of the tables.
It should not be such that the user has to key in the date+shift three
times for 3 delete operations related to the above 3 tables.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
The delete operation will find out the records in each of the 3 tables
and delete accordingly.
After deletion, a message needs to be displayed that ''All records
for .....date... and ... shift.. have been deleted''.
If the user keys in a combination (date + shift), whose records are
non existent in the tables, then a message shall be displayed that ''No
such records exist''.
Thanks a lot for the help.
shriil
推荐答案
你只有三张桌子，所以你没有很多关系。记下
您的所有关系，以便以后可以重新创建它们。现在删除所有
关系。创建一个名为ZZTempTable的新临时表，并创建
两个字段：Date和Shift。将两个字段都设为主键。现在输入一个
记录：你要删除的日期和你想要的转移（1,2或3）
删除。
现在在ZZTempTable和Date中创建Date和Shift之间的关系，并在其他三个表中创建
Shift。当你创建每个关系时，请确保
来检查级联删除。
创建所有关系后，您现在可以删除所有
三个表中的记录，其中包含您想要的日期和班次
删除。只需打开ZZTempTable并删除那里的一条记录。您将收到有关级联删除的
a消息并回复是。
要将数据库恢复原状，请删除所有
ZZTempTable与其他三个表之间的关系。现在删除
ZZTempTable。最后，获取您在三个表之间编写原始
关系的文件，并重新创建所有关系。
您的数据库将恢复到原始状态，没有任何记录
日期和转移你删除。
顺便说一下---
" Date"在Access中是一个保留字，不应该用于字段名称。
比较晚，使用日期作为字段名称将导致你的问题。
建议 -
造成问题的根本原因可能是你的桌子没有正确设计。你应该看看你的桌子的设计
看看它们是否设计正确。
PC数据表
提供客户访问，Excel和Word帮助的资源
应用程序
re ****** @ pcdatasheet.com
" shriil" < sa *********** @ gmail.comwrote in message
news：11 ******************* ***@i13g2000prf.googlegr oups.com ...
You only have three tables so you don''t have many relationships. Write down
all your relationships so you can later recreate them. Now delete all your
relationships. Create a new temporary table named ZZTempTable, and create
two fields: Date and Shift. Make both fields the primary key. Now enter one
record: The Date you want to delete and the Shift (1, 2 or 3) you want to
delete.
Now create a relationship between Date and Shift in ZZTempTable and Date and
Shift in the other three tables. When you create each relationship, be sure
to check Cascade Delete.
After all the relationships are created, you will now be able to delete all
the records in your three tables that have the Date and Shift you want to
delete. Just open ZZTempTable and delete the one record there. You will get
a message about cascade deleting and respond Yes.
To restore your database back to the way it was, delete all the
relationships between ZZTempTable and the other three tables. Now delete
ZZTempTable. Finally, get the paper where you wrote the original
relationships between the three tables and recreate all the relationships.
Your database will be back to original condition without any records for the
date and shift you deleted.
By the way ---
"Date" is a reserved word in Access and should not be used for a field name.
Sooner than later, using Date for a field name is going to cause you a
problem.
A recommendation --
The root cause of what caused your problem is probably that your tables are
not designed correctly. You ought to look at the design of your tables to
see if they are designed correctly.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"shriil" <sa***********@gmail.comwrote in message
news:11**********************@i13g2000prf.googlegr oups.com...
嗨
我有这个计算和存储的数据库特定部门员工获得的奖励金额
。通过输入日期，班次（早上，晚上或晚上）和''员工
名称''输入每条记录
。还有另一张桌子可以为班次分配一张身份证，即早上和晚上为
1,2和3。夜班分别。从母亲
表中，每位员工的日期计算奖励按照他/她的班次来计算。总的来说，数据库填充在3个表中，
，''出勤''，出勤最终''和''奖励''，每个
有Date，Shift和&员工字段。
现在碰巧由于错误的数据输入，删除与特定日期有关的所有记录都需要&b; b />
从上面的每个表中移位。这更是如此，因为所有表格都有多个主键，并且通过追加查询形成了表格，我没有任何表格。其他选择，但
删除与该特定日期相关的所有记录+从每个表格中转移
。删除后，操作员将能够为该特定日期+班次重新输入新的
数据，查询将重新计算
并相应地形成表格。
我想知道怎么做。我不太熟悉
删除查询或VBA。
最初，我希望用户首先需要输入
日期
以及他希望从表格中每个
删除记录的班次。
它应该对于与上述3个表相关的3个删除操作，用户必须键入日期+转换三个
次。
键入后，用户需要在启动
删除操作之前再次重新确认。
删除操作将找出3个表中每个表中的记录
并相应删除。
删除后，需要显示一条消息''所有记录
... ..date ...和... shift ..已被删除''。
如果用户键入组合（日期+班次），其记录为
表中不存在，然后会显示一条消息那个'没有
这样的记录存在''。
非常感谢你的帮助。
shriil
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the ''employee
name''. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In total, the database is populated in 3 tables,
namely, ''Attendance'', Attendance Final'' and ''Incentive'', each of
which
has the Date, Shift, & employee field.
Now it so happens that due to erroneous data entry, it becomes
necessary for deleting all records pertaining to a particular date &
shift from each of the above tables. This is more so because as all
the tables have multiple primary keys, and sone of the tables are
formed through ''Append'' queries, I do not have any other option but to
delete all records related to that particular date+shift from each of
the tables. After deletion, the operator will be able to reenter fresh
data for that particular date+shift and the queries will recalculate
and form tables accordingly.
I would like to know how to go about this. I am not quite conversant
with the delete queries or with VBA.
Initially, I would prefer that the user first needs to key in the
date
and the shift for which he wants the records to be deleted from each
of the tables.
It should not be such that the user has to key in the date+shift three
times for 3 delete operations related to the above 3 tables.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
The delete operation will find out the records in each of the 3 tables
and delete accordingly.
After deletion, a message needs to be displayed that ''All records
for .....date... and ... shift.. have been deleted''.
If the user keys in a combination (date + shift), whose records are
non existent in the tables, then a message shall be displayed that ''No
such records exist''.
Thanks a lot for the help.
shriil
" Steve" < so *** @ private.emailaddressschreef in bericht news：13 ************* @ corp.supernews.com ...
"Steve" <so***@private.emailaddressschreef in bericht news:13*************@corp.supernews.com...
你只有三张桌子，所以你没有很多关系。记下
您的所有关系，以便以后可以重新创建它们。现在删除所有
关系。创建一个名为ZZTempTable的新临时表，并创建
两个字段：Date和Shift。将两个字段都设为主键。现在输入一个
记录：你要删除的日期和你想要的转移（1,2或3）
删除。
现在在ZZTempTable和Date中创建Date和Shift之间的关系，并在其他三个表中创建
Shift。当你创建每个关系时，请确保
来检查级联删除。
创建所有关系后，您现在可以删除所有
三个表中的记录，其中包含您想要的日期和班次
删除。只需打开ZZTempTable并删除那里的一条记录。您将收到有关级联删除的
a消息并回复是。
要将数据库恢复原状，请删除所有
ZZTempTable与其他三个表之间的关系。现在删除
ZZTempTable。最后，获取您在三个表之间编写原始
关系的文件，并重新创建所有关系。
您的数据库将恢复到原始状态，没有任何
日期的记录，并且您已删除了。
顺便说一下---
日期是Access中的保留字，不应用于字段名称。
比以后更早，使用日期作为字段名称会导致你的问题。
推荐 -
导致问题的根本原因可能是您的表格没有正确设计。您应该查看表格的设计
看看它们是否设计正确。
PC数据表
You only have three tables so you don''t have many relationships. Write down
all your relationships so you can later recreate them. Now delete all your
relationships. Create a new temporary table named ZZTempTable, and create
two fields: Date and Shift. Make both fields the primary key. Now enter one
record: The Date you want to delete and the Shift (1, 2 or 3) you want to
delete.
Now create a relationship between Date and Shift in ZZTempTable and Date and
Shift in the other three tables. When you create each relationship, be sure
to check Cascade Delete.
After all the relationships are created, you will now be able to delete all
the records in your three tables that have the Date and Shift you want to
delete. Just open ZZTempTable and delete the one record there. You will get
a message about cascade deleting and respond Yes.
To restore your database back to the way it was, delete all the
relationships between ZZTempTable and the other three tables. Now delete
ZZTempTable. Finally, get the paper where you wrote the original
relationships between the three tables and recreate all the relationships.
Your database will be back to original condition without any records for the
date and shift you deleted.
By the way ---
"Date" is a reserved word in Access and should not be used for a field name.
Sooner than later, using Date for a field name is going to cause you a
problem.
A recommendation --
The root cause of what caused your problem is probably that your tables are
not designed correctly. You ought to look at the design of your tables to
see if they are designed correctly.
PC Datasheet
哇!!
我不相信我曾经看过这样一个嘲笑的回答...
你的建议是让普通用户创建/删除关系???很好!!
如果存在关系（我们甚至不知道），那么按照你的说法做一个非常糟糕的*操作：
" ;写下关系，删除它们并在删除完成后重新创建关系......
同样在多用户情况下，这将是一场灾难。先把所有其他人踢出去？
但你也忘了一件小事：
Wow!!
I don''t believe I EVER saw such a ridicule answer...
Your advise is to let common users create/delete relations ??? Nice!!
IF there were relations (which we don''t even know), it would be a very *nasty* operation to do what you say:
"Write the relations down, delete them and recreate the relations after the delete is done..."
Also in a multi-user situation this would be a disaster. First kick all the others out ??
But you also forgot one minor thing:
现在在Date之间创建一个关系和ZZTempTable中的Shift和日期以及
在其他三个表中移位。当您创建每个关系时，请确保
以检查Cascade Delete。
Now create a relationship between Date and Shift in ZZTempTable and Date and
Shift in the other three tables. When you create each relationship, be sure
to check Cascade Delete.
IMO不可能在ZZTempTable与其他表之间创建所需的（RI）关系而不删除* all *没有Shift的记录和你的Temptable的日期（PK）价值。
也许你忘了对OP说：请先写下所有这些记录???
所以现在我们留下了三张桌子，只有我们要删除的记录....很好!!删除它们!!
在此之后，由于新的关系，添加其他记录（首先标记已删除的记录！）甚至不可能....
解决方案??
也许最初*全部*现有的Date和Shift组合需要在新表中输入？在创建RI关系之前？
我们怎么做？啊，通过一些查询''s ??
因为OP不太熟悉使用删除查询's'，也许他/她熟悉追加查询''s ??
我可能会错过这里明显的东西，或者我可能只是错了但是...... 。
我想只需将所有数据刻在石头上就可以更快更安全地扔掉一些石头......
顺便说一句：我看到你发布的是超级新闻现在??
他们是否将你踢出Earthlink？
Arno R
IMO it is impossible to create the needed (RI) relation between ZZTempTable and the other tables without deleting *all* the records that don''t have the Shift and Date (PK) values of your Temptable.
Maybe you forgot to say to the OP: Please write down all these records first ???
So now we are left with three tables with only the records that we want to delete.... Nice !! Delete them!!
After this, adding the other records (first mark the deleted ones!) back would not even be possible because of the new relation....
Solution??
Maybe at first *all* the existing combinations of Date and Shift needed to be entered in the new table ?? BEFORE creating the RI relation??
HOW would we do that?? Ahhh by means of a few query''s ??
Since the OP is "not quite conversant" with delete query''s, maybe he/she is conversant with append-query''s ??
I might miss something obvious here, or maybe I am just mistaken but...
I guess it would be quicker and safer to just carve all the data in stone, and throw some stones away...
BTW: I see you are posting from Supernews now ??
Did they kick you out at Earthlink ??
Arno R
我们是否可以获得有关表格如何参加和出席的更多信息
最终互动？此外，附加查询如何适应场景？
无需删除关系或创建临时
表。每个表的简单删除查询就足够了。
因为你不关心转移，所以这不会是
删除查询中的一个因素。
John ... Visio MVP
" shriil" < sa *********** @ gmail.comwrote in message
news：11 ******************* ***@i13g2000prf.googlegr oups.com ...
Can we get more information about how the tables Attendance and Attendance
Final interact? Also, how do the Append Queries fit into the scenario?
There is no need for removing the relationships or creating a temporary
table. A simple delete query for each table should be enough.
Since you do not care about the shift, that will not be a factor in the
delete query.
John... Visio MVP
"shriil" <sa***********@gmail.comwrote in message
news:11**********************@i13g2000prf.googlegr oups.com...
>
我有这个数据库来计算和存储激励金额
由特定部门的员工赚取。通过输入日期，班次（早上，晚上或晚上）和''员工
名称''输入每条记录
。还有另一张桌子可以为班次分配一张身份证，即早上和晚上为
1,2和3。夜班分别。从母亲
表中，每位员工的日期计算奖励按照他/她的班次来计算。总的来说，数据库填充在3个表中，
，''出勤''，出勤最终''和''奖励''，每个
有Date，Shift和&员工字段。
现在碰巧由于错误的数据输入，删除与特定日期有关的所有记录都需要&b; b />
从上面的每个表中移位。这更是如此，因为所有表格都有多个主键，并且通过追加查询形成了表格，我没有任何表格。其他选择，但
删除与该特定日期相关的所有记录+从每个表格中转移
。删除后，操作员将能够为该特定日期+班次重新输入新的
数据，查询将重新计算
并相应地形成表格。
我想知道怎么做。我不太熟悉
删除查询或VBA。
最初，我希望用户首先需要输入
日期
以及他希望从表格中每个
删除记录的班次。
它应该对于与上述3个表相关的3个删除操作，用户必须键入日期+转换三个
次。
键入后，用户需要在启动
删除操作之前再次重新确认。
删除操作将找出3个表中每个表中的记录
并相应删除。
删除后，需要显示一条消息''所有记录
... ..date ...和... shift ..已被删除''。
如果用户键入组合（日期+班次），其记录为
表中不存在，然后会显示一条消息那个'没有
这样的记录存在''。
非常感谢你的帮助。
shriil
>
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the ''employee
name''. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In total, the database is populated in 3 tables,
namely, ''Attendance'', Attendance Final'' and ''Incentive'', each of
which
has the Date, Shift, & employee field.
Now it so happens that due to erroneous data entry, it becomes
necessary for deleting all records pertaining to a particular date &
shift from each of the above tables. This is more so because as all
the tables have multiple primary keys, and sone of the tables are
formed through ''Append'' queries, I do not have any other option but to
delete all records related to that particular date+shift from each of
the tables. After deletion, the operator will be able to reenter fresh
data for that particular date+shift and the queries will recalculate
and form tables accordingly.
I would like to know how to go about this. I am not quite conversant
with the delete queries or with VBA.
Initially, I would prefer that the user first needs to key in the
date
and the shift for which he wants the records to be deleted from each
of the tables.
It should not be such that the user has to key in the date+shift three
times for 3 delete operations related to the above 3 tables.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
The delete operation will find out the records in each of the 3 tables
and delete accordingly.
After deletion, a message needs to be displayed that ''All records
for .....date... and ... shift.. have been deleted''.
If the user keys in a combination (date + shift), whose records are
non existent in the tables, then a message shall be displayed that ''No
such records exist''.
Thanks a lot for the help.
shriil
这篇关于一次删除多个表中的选定记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
