Deleting of Select Records from multiple tables

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

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.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
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

 

[scubadiver]

The first thing I would ask is if this is a one off set of deletions because
I personally think the design should be improved.

A delete query is just a normal select query. In the criteria row you can
write a reference to unbound text boxes in a form where you enter your info
like so:

[forms]![form name]![field name]

Then you can create a command button in the form which runs the delete query
using the criteria you specify.

 

[scubadiver]

I forgot to add. If the delete query find no records a message like "The
query will delete zero records" will appear (or something like that)

 

[shriil]

The first thing I would ask is if this is a one off set of deletions because
I personally think the design should be improved.

A delete query is just a normal select query. In the criteria row you can
write a reference to unbound text boxes in a form where you enter your info
like so:

[forms]![form name]![field name]

Then you can create a command button in the form which runs the delete query
using the criteria you specify.

--www.ae911truth.org

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.

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.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
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 -

Thanks for the info. Yes it is a set of deletions that may happen
sometimes as it depends totally upon the initial data entry by the
operator which, unfortunately, may be found later to be erroneous.

As the tables have multiple primary keys, in the event when the error
is detected, i dont have any option but to delete all records
pertaining to that particular date+shift from each of the three
tables, so as to enable the operator again to enter fresh correct data
for that particular date+shift.

About your advise regarding writing a reference to unbound text boxes
in a form which is linked with the criteria row of the delete query,
could you please explain it a little more on how to link a form with
the criteria row. Is it possible for asking the user again to
reconfirm the date & shift he has entered, before i run the delete
query?

Thanks for the help

 

[scubadiver]

In a databases I have a form for exporting information to excel. In this
form I have two combo boxes (one for start date and one for end date). Based
on what dates I select I can choose what data to export.

The form may be called [reports], the start date combo maybe called
[DateFrom] and the end date combo maybe called [DateTo].

If I wanted to delete a set of records between two dates I would put the
following in the criteria row of the "date" field in the delete query:

=[forms]![reports]![DateFrom] and <=[forms]![reports]![DateTo]

On the click event in the command button to run the query:

docmd.setwarnings = false
If MsgBox ("Are you sure you would like to delete this set of records?",
vbYesNo) = vbYes Then
docmd.openquery ....etc
End If

When you click "Yes", the query will run, if "no" then the code will not run.

Let me know!

--
www.ae911truth.org

The first thing I would ask is if this is a one off set of deletions because
I personally think the design should be improved.

A delete query is just a normal select query. In the criteria row you can
write a reference to unbound text boxes in a form where you enter your info
like so:

[forms]![form name]![field name]

Then you can create a command button in the form which runs the delete query
using the criteria you specify.

--www.ae911truth.org

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.

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.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
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 -

Thanks for the info. Yes it is a set of deletions that may happen
sometimes as it depends totally upon the initial data entry by the
operator which, unfortunately, may be found later to be erroneous.

As the tables have multiple primary keys, in the event when the error
is detected, i dont have any option but to delete all records
pertaining to that particular date+shift from each of the three
tables, so as to enable the operator again to enter fresh correct data
for that particular date+shift.

About your advise regarding writing a reference to unbound text boxes
in a form which is linked with the criteria row of the delete query,
could you please explain it a little more on how to link a form with
the criteria row. Is it possible for asking the user again to
reconfirm the date & shift he has entered, before i run the delete
query?

Thanks for the help

 

[shriil]

In a databases I have a form for exporting information to excel. In this
form I have two combo boxes (one for start date and one for end date). Based
on what dates I select I can choose what data to export.

The form may be called [reports], the start date combo maybe called
[DateFrom] and the end date combo maybe called [DateTo].

If I wanted to delete a set of records between two dates I would put the
following in the criteria row of the "date" field in the delete query:

=[forms]![reports]![DateFrom] and <=[forms]![reports]![DateTo]

On the click event in the command button to run the query:

docmd.setwarnings = false
If MsgBox ("Are you sure you would like to delete this set of records?",
vbYesNo) = vbYes Then
docmd.openquery ....etc
End If

When you click "Yes", the query will run, if "no" then the code will not run.

Let me know!

--www.ae911truth.org

The first thing I would ask is if this is a one off set of deletions because
I personally think the design should be improved.
A delete query is just a normal select query. In the criteria row you can
write a reference to unbound text boxes in a form where you enter your info
like so:
[forms]![form name]![field name]
Then you can create a command button in the form which runs the delete query
using the criteria you specify.
--www.ae911truth.org
:
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.
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.
After keying in, the user needs to reconfirm again the same before the
delete operation is initiated.
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 -

Thanks for the info. Yes it is a set of deletions that may happen
sometimes as it depends totally upon the initial data entry by the
operator which, unfortunately, may be found later to be erroneous.

As the tables have multiple primary keys, in the event when the error
is detected, i dont have any option but to delete all records
pertaining to that particular date+shift from each of the three
tables, so as to enable the operator again to enter fresh correct data
for that particular date+shift.

About your advise regarding writing a reference to unbound text boxes
in a form which is linked with the criteria row of the delete query,
could you please explain it a little more on how to link a form with
the criteria row. Is it possible for asking the user again to
reconfirm the date & shift he has entered, before i run the delete
query?

Thanks for the help- Hide quoted text -

- Show quoted text -

Thks again. Will get back to you ASAP.