473,544 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting selected records from multiple tables at one go

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

Aug 8 '07 #1
11 3652
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******@pcdata sheet.com


"shriil" <sa***********@ gmail.comwrote in message
news:11******** **************@ i13g2000prf.goo glegroups.com.. .
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

Aug 8 '07 #2
"Steve" <so***@private. emailaddresssch reef in bericht news:13******** *****@corp.supe rnews.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

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:
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 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
Aug 9 '07 #3
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.goo glegroups.com.. .
>
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

Aug 9 '07 #4
On Aug 9, 9:57 pm, "John Marshall, MVP" <lancu...@stone henge.ca>
wrote:
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" <sanjib.lah...@ gmail.comwrote in message

news:11******** **************@ i13g2000prf.goo glegroups.com.. .


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- Hide quoted text -

- Show quoted text -
Hi John

Thks for asking.

The first table, 'Attendance' is populated through a userform being
filled in by the operator. By way of design of the primary keys for
this table, the Operator can enter all records pertaining to a
particular date+shift at one go only. When he closes the form, the
Attendance table sorts itself and appends to the 'Attendance Final'
table. On reopening the user form, the operator can view which dates
+shifts that have already been recorded, thru another pop up form.
After he closes the popup form, he can enter new data but he will be
unable to enter data with regard to a date+shift which has already
been entered. This has been done to avoid duplicating entries and for
security purpose. Subsequently from the 'Attendance Final' the
'Incentive' table is appended which has fields that calculate the
incentive amount applicable for each employee for that particular
shift.

Now, in the event the operator has entered wrong employee information
pertaining to a particular date+shift, which sometimes happen, as the
source data from which the operator assimilates, is handwritten and
sometimes illegible, it is not possible for him to reenter new data
pertaining to that date+shift as described above unless and until I
remove all records related to that particular date+shift from the
'Attendance' Table. Yes, he has to do a bit of extra work by the fact
that he has to again reenter all the employee names that fell under
that date+shift, besides the ones that he is required to do. Just
deleting the select records from the Attendance table will not
suffice. I have to delete the same from the 'Attendance Final' and
'Incentive' table also.

As you opined, I need simple delete queries. Contrary to what Steve
said, I am quite happy with the design of my tables, and I guess he
could not understand my simple requirement. My question was how to go
about this, while considering the following conditions: I append below
the same stuff which I had written in my first message.

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'.
I hope that I have been able to make things clear.

Thks again for the help

Aug 10 '07 #5
"Steve" <so***@private. emailaddresswro te in message
news:13******** *****@corp.supe rnews.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.
<snipped drivel>

Look at these three sentences:

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

All three contain the phrase "the user". Your suggestion is mind-bogglingly
stupid given that the OP wants this to happen at runtime.

Aug 10 '07 #6
On Aug 10, 2:59 pm, "Keith Wilby" <h...@there.com wrote:
"Steve" <so...@private. emailaddresswro te in message

news:13******** *****@corp.supe rnews.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.

<snipped drivel>

Look at these three sentences:

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

All three contain the phrase "the user". Your suggestion is mind-bogglingly
stupid given that the OP wants this to happen at runtime.
Why is it 'mind boggingly stupid?. The 'User' in this case is the
'Operator' who has come to understand that a particular set of records
for a particular date+shift has some erroneous employee names for
which the whole set needs to be deleted such that he can reenter
correct data for that particular date+shift.

NOW, I would like to have a code where the User (Operator) will go
through the above-mentioned actions before he actually deletes that
particular set of records.

Please note this type of problem is not theoritical, but a practical
one that I have been facing with .

I can't be more lucid than this. Ty

Aug 10 '07 #7

"shriil" <sa***********@ gmail.comwrote in message
news:11******** **************@ i13g2000prf.goo glegroups.com.. .
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

Aug 10 '07 #8

"shriil" <sa***********@ gmail.comschree f in bericht news:11******** **************@ i38g2000prf.goo glegroups.com.. .
On Aug 9, 9:57 pm, "John Marshall, MVP" <lancu...@stone henge.ca>
wrote:
>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" <sanjib.lah...@ gmail.comwrote in message

news:11******* *************** @i13g2000prf.go oglegroups.com. ..


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- Hide quoted text -

- Show quoted text -
Hi John

Thks for asking.

The first table, 'Attendance' is populated through a userform being
filled in by the operator. By way of design of the primary keys for
this table, the Operator can enter all records pertaining to a
particular date+shift at one go only. When he closes the form, the
Attendance table sorts itself and appends to the 'Attendance Final'
table. On reopening the user form, the operator can view which dates
+shifts that have already been recorded, thru another pop up form.
After he closes the popup form, he can enter new data but he will be
unable to enter data with regard to a date+shift which has already
been entered. This has been done to avoid duplicating entries and for
security purpose. Subsequently from the 'Attendance Final' the
'Incentive' table is appended which has fields that calculate the
incentive amount applicable for each employee for that particular
shift.

Now, in the event the operator has entered wrong employee information
pertaining to a particular date+shift, which sometimes happen, as the
source data from which the operator assimilates, is handwritten and
sometimes illegible, it is not possible for him to reenter new data
pertaining to that date+shift as described above unless and until I
remove all records related to that particular date+shift from the
'Attendance' Table. Yes, he has to do a bit of extra work by the fact
that he has to again reenter all the employee names that fell under
that date+shift, besides the ones that he is required to do. Just
deleting the select records from the Attendance table will not
suffice. I have to delete the same from the 'Attendance Final' and
'Incentive' table also.

As you opined, I need simple delete queries. Contrary to what Steve
said, I am quite happy with the design of my tables, and I guess he
could not understand my simple requirement. My question was how to go
about this, while considering the following conditions: I append below
the same stuff which I had written in my first message.

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'.
I hope that I have been able to make things clear.

Thks again for the help
I guess you need something like this: (Pseudocode mostly)

-- Create a form (FrmDeleteShift s) where user can key-in Date and Shift
-- Create a DeleteAction-button
When clicked validate existance of records first: (Using DCount perhaps??)
-- Validation: Are there existing records for this date and shift?
If No ==Msgbox: "No records to delete" code ends here
If Yes: Msgbox: Ask confirmation to delete.
-- If Msgbox ("Proceed ??", vbQuestion + vbYesNo, "Confirmation") =vbYes then
After confirmation: Delete the records by means of three deletequery's (params come from the form)
-- CurrentDb.execu te "DeleteQuer y1", dbFailonerror
-- CurrentDb.execu te "DeleteQuer y2", dbFailonerror
-- CurrentDb.execu te "DeleteQuer y3", dbFailonerror
-- No errors==Msgbox "Deletion finished"

The delete-query's must be so-called parameterquery' s.
So in the columns for Shift and Date you need to enter at the row where it says "Criteria":
Forms!FrmDelete Shifts!Shift and Forms!FrmDelete Shifts!Date
Btw: (Steve is right on the Date-issue; Better choose another FieldName like DeleteDate or so in your tables and query's)
Be sure to test the query's first. Make sure they work
When you use a form like this the user only needs to enter the params once

If you need more help... let us know where you are stuck.

Arno R
Aug 10 '07 #9

"shriil" <sa***********@ gmail.comwrote
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'.
It appears to me that you're asking for a sub-application, a form and VBA
code to execute queries and return messages. That's a bit much for a
newsgroup response... it's the approach I'd take if this were to be a
regular occurrence; but it seems overkill for a probably-one-time thing you
can do with three, simple delete queries.

I don't see why you would need to delete relationships.. . as long as you
delete the records from the child table before you delete the records from
the parent table, you can just do it with three delete queries. I'd
suggest, if you have access to the database (that is, if it is not in a
separate physical location) that you do just that.

First, just as insurance, make a copy of your database; then, in the Query
builder create a new Query, add the lowest level of child table as Data
Source, then pull down the date and shift fields (BTW, "Date" is not good
for a Field name, as it is an Access reserved word, and can lead to
confusion.). In the Criteria Lines below them enter the date and shift you
want to delete. Add a few other fields, just so you can see what you have.
Run the Query... does it return what you expected? If so, good. Now, go
back to Query Builder view, and on the menu, Query, and Delete Query. Then
run the delete Query.

Second, repeat for the other same-level, or higher-level child table.

Finally, repeat for the primary table.

And, there you are.

Larry Linson
Microsoft Access MVP
Aug 10 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6301
by: DC Gringo | last post by:
I'm having such a problem with this DropDownList in a user control that is posting back and throwing an error: System.Web.HttpException: A DropDownList cannot have multiple items selected <ASP:DROPDOWNLIST ID="lbCountriesWiz" ENABLEVIEWSTATE="true" FONT-SIZE="8pt" ONSELECTEDINDEXCHANGED="ddlQueryProvinces" AUTOPOSTBACK="True"...
5
2510
by: Alex | last post by:
Hi, I have two tables , A and B where table B has a foreign key constraint to table A. I want to delete all records in table A that are older than a certain date that are not referenced by table B. When I use a DELETE FROM the entire transaction fails as soon as a referential integrity violation is detected.
1
6090
by: Mark | last post by:
This question refers to a main form with a continuous form subform. After an error occurs after entering several records in the subform, how can I delete all the data in the main form and all the records in the subform? I have tried undoing both the main form and the subform and I have tried deleting the record in the main form. Thanks! ...
5
14718
by: Mojtaba Faridzad | last post by:
Hi, with SetDataBinding( ) a DataGrid shows a DataView. user can select some rows in the grid by holding cotrol key. when user clicks on Delete button, I should delete all selected rows. I am trying to delete these lines from the dataview like this: for (int i=0; i < dataView.Count; i++) if (dataGrid.IsSelected(i)) dataView.Delete(i);
5
3755
by: Robert Brown | last post by:
Hi All. I have a routine that checks a SQL Table for all records 3 months prior to a predetermined date, then I insert them into an Archive DB then delete those records from the original table. When I do a "select" for the records, I load them into a dataset, use an "insert" statement to insert the info into the second table (by a for...
7
6591
by: Susan Mackay | last post by:
I have a data table that is connected to a database table with a data adapter in the 'standard' manner. However I want to be able to remove selected rows from the data table (i.e. no longer include them in the set that is displayed to the user) but I don't want to delete the corresponding row from the database. I've tried using the...
4
6121
by: rn5a | last post by:
I am binding a DropDownList with records existing in a database table. I want to add an extra item *SELECT COMPANY* at index 0 so that by default, it gets selected. This is how I tried it but the extra item just doesn't get added to the DropDownList: ============================================= <script runat="server"> Sub...
5
1896
by: jasone | last post by:
Hi all, im nearly there with this one and im sure it shouldnt be hard to solve, i just cant seem to find the solution. ive got records being displayed, the user can then tick what records to delete... click delete.. it shows deleted records then you can go back to the screen, It works if one record is selected but any more it doesnt delete...
2
2104
by: padmaneha | last post by:
Hi I have created two tables 'TrainsMaster' & 'TransArrvlDepinfo' Columns which I have created in 'TrainsMaster' are 'trainName,TrainNo, StartStaionId, & EndstationId' Columns which I have created in ''TransArrvlDepinfo' are 'Stationcode, TrainNo, Arrvaltime,Depttime' I have to delete few trains from 'TrainsMaster' for which the...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7781
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5306
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3427
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
677
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.