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
11  3639 
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...
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...
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
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...
>
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
On Aug 9, 9:57 pm, "John Marshall, MVP" <lancu...@stonehenge.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.googlegr oups.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
"Steve" <so***@private.emailaddresswrote in message
news:13*************@corp.supernews.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.
On Aug 10, 2:59 pm, "Keith Wilby" <h...@there.comwrote:
"Steve" <so...@private.emailaddresswrote in message
news:13*************@corp.supernews.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
"shriil" <sa***********@gmail.comwrote in message
news:11**********************@i13g2000prf.googlegr oups.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
"shriil" <sa***********@gmail.comschreef in bericht news:11**********************@i38g2000prf.googlegr oups.com...
On Aug 9, 9:57 pm, "John Marshall, MVP" <lancu...@stonehenge.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.googleg roups.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 (FrmDeleteShifts) 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.execute "DeleteQuery1", dbFailonerror
-- CurrentDb.execute "DeleteQuery2", dbFailonerror
-- CurrentDb.execute "DeleteQuery3", 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!FrmDeleteShifts!Shift and Forms!FrmDeleteShifts!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
"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
On Aug 11, 12:14 am, "Arno R" <arracomn_o_s_p_...@planet.nlwrote:
"shriil" <sanjib.lah...@gmail.comschreef in berichtnews:11**********************@i38g2000prf.g ooglegroups.com...
On Aug 9, 9:57 pm, "John Marshall, MVP" <lancu...@stonehenge.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.googleg roups.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 (FrmDeleteShifts) 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.execute "DeleteQuery1", dbFailonerror
-- CurrentDb.execute "DeleteQuery2", dbFailonerror
-- CurrentDb.execute "DeleteQuery3", 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!FrmDeleteShifts!Shift and Forms!FrmDeleteShifts!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- Hide quoted text -
- Show quoted text -
Dear Arno
Thks. I will do as advised. Will get back to u
"Steve" <so***@private.emailaddresswrote:
>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.
Back from holidays.
Steve
Your suggested solution is exceedingly impractical.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
| |
