Direction for deleting records stored in two tables

[SodaJim]

Hello everyone,

I'm looking for a possible solution for deleting a record(s) via a cmdButton
or another method that will remove a record in a table but will also need to
subtract a value from another table field and replace this with the result...

My end users from time to time will inadvertantly click one of the three
cmdButtons and a later time, need to remove a record as well deduct the value
in hours from another table... Currently, I manually go in and perform this
task but would like to automate this task through permissions available to a
particular group of users...

The code below is currently executed when a cmdButton is clicked. There are
two additional buttons with very similar code that may be clicked. I'm
showing this for reference so you can see the process of record additions.

Thanks for any suggestions or input!
Jim

========================
Private Sub cmdCalled_Click()
Dim myMsg, mySQL As String
Dim otDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset

otDate = Forms!frmOvertime![txtOT_Date]
'Double check to see if the OT Hours is Zero
If Forms!frmOvertime![txtOT_Hours] <= 0 Then
myMsg = MsgBox("The Overtime Hours are set to 0" & (Chr(13)) &
"Please enter the number of hours!", vbCritical, "Missing Hours")
Forms!frmOvertime!OT_Hours.SetFocus
Else
myMsg = "Data has been Modified!"
myMsg = myMsg & (Chr(13)) & "Do wish to Save the Changes?"
myMsg = myMsg & (Chr(13)) & "Click YES to Save or No to Discard
Changes."
If MsgBox(myMsg, vbQuestion + vbYesNo, "Save Changes?") =
vbYes Then
'Update Employee Total OT Hours
Forms!frmOvertime!frmEmployees![txtOT_Total_Hours] =
(Forms!frmOvertime!frmEmployees![txtOT_Total_Hours] +
(Forms!frmOvertime![txtOT_Hours] / 2))
'Adding new record in tblEmployeeOvertimes
mySQL = "Insert into tblEmployeeOvertimes (EmployeeID,
OT_CalledDate, OT_NoContact, OT_Hours, OT_Hours_Actual) " & _
"Values ('" & Me.EmployeeID & "', '" & otDate &
"', (-1), '" & Forms!frmOvertime![txtOT_Hours] & "', '" &
(Forms!frmOvertime![txtOT_Hours] / 2) & "')"
DBEngine(0)(0).Execute mySQL, dbFailOnError
'==========================================
DoCmd.Save
Me.Requery
'Code to Disable Command Buttons
cmdCalled.Enabled = False
cmdDenied.Enabled = False
cmdWorked.Enabled = False
Else
DoCmd.RunCommand acCmdUndo
End If
End If
End Sub
=========================

 

[Jeff Boyce]

I'm not entirely clear on your data structure (tables), but it sounds like
you have one table with individual entries and another table that is only a
summary ("remove in a table ... subtract [in] another table").

If so, why? Again, I'm not there and don't know your requirements, but it
seems possible to do a Totals query on the first table and come up with the
calculated total, rather than using a second table.


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello everyone,

I'm looking for a possible solution for deleting a record(s) via a cmdButton
or another method that will remove a record in a table but will also need to
subtract a value from another table field and replace this with the result...

My end users from time to time will inadvertantly click one of the three
cmdButtons and a later time, need to remove a record as well deduct the value
in hours from another table... Currently, I manually go in and perform this
task but would like to automate this task through permissions available to a
particular group of users...

The code below is currently executed when a cmdButton is clicked. There are
two additional buttons with very similar code that may be clicked. I'm
showing this for reference so you can see the process of record additions.

Thanks for any suggestions or input!
Jim

========================
Private Sub cmdCalled_Click()
Dim myMsg, mySQL As String
Dim otDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset

otDate = Forms!frmOvertime![txtOT_Date]
'Double check to see if the OT Hours is Zero
If Forms!frmOvertime![txtOT_Hours] <= 0 Then
myMsg = MsgBox("The Overtime Hours are set to 0" & (Chr(13)) &
"Please enter the number of hours!", vbCritical, "Missing Hours")
Forms!frmOvertime!OT_Hours.SetFocus
Else
myMsg = "Data has been Modified!"
myMsg = myMsg & (Chr(13)) & "Do wish to Save the Changes?"
myMsg = myMsg & (Chr(13)) & "Click YES to Save or No to Discard
Changes."
If MsgBox(myMsg, vbQuestion + vbYesNo, "Save Changes?") =
vbYes Then
'Update Employee Total OT Hours
Forms!frmOvertime!frmEmployees![txtOT_Total_Hours] =
(Forms!frmOvertime!frmEmployees![txtOT_Total_Hours] +
(Forms!frmOvertime![txtOT_Hours] / 2))
'Adding new record in tblEmployeeOvertimes
mySQL = "Insert into tblEmployeeOvertimes (EmployeeID,
OT_CalledDate, OT_NoContact, OT_Hours, OT_Hours_Actual) " & _
"Values ('" & Me.EmployeeID & "', '" & otDate &
"', (-1), '" & Forms!frmOvertime![txtOT_Hours] & "', '" &
(Forms!frmOvertime![txtOT_Hours] / 2) & "')"
DBEngine(0)(0).Execute mySQL, dbFailOnError
'==========================================
DoCmd.Save
Me.Requery
'Code to Disable Command Buttons
cmdCalled.Enabled = False
cmdDenied.Enabled = False
cmdWorked.Enabled = False
Else
DoCmd.RunCommand acCmdUndo
End If
End If
End Sub
=========================

 

[SodaJim]

Thanks for the reply Jeff!

I'll attempt to briefly explain:

tblBasicInfo - stores static info about each employee except Total Over Time
hours, this number increases depending on accepting an overtime slot, denying
an OT slot OR if an employee cannot be reached. This creates a chronological
order of employees based on their Total Overtime Hours.
tblEmployeeOvertimes - stores each instance an employee works, denies or
cannot be contacted for overtime. I guess this could be called overtime
details
tblOvertime - stores the Overtime records

Methodology:
An Overtime record is added to tblOvertime,
For every record in tblOvertime, there are associated records in
tblEmployeeOvertimes. IE Three people are contacted to fill the OT, one
declines, another cannot be reached and the last person works. Each of these
three choices affects the Total Overtime Hours for each person contacted: No
Contact will only add half of the overtime hours to the total whereas, denied
and worked will increase by the actual number of overtime hours to be worked.

Hopefully, this paints a fairly clear picture...

Now to my delima:
End users click a button that will create a record in tblEmployeeOvertimes
and increment the field Total Overtime Hours in the tblBasicInfo... Well this
person may not qualify for the Overtime and should have never been contacted
in the first place! The record needs to be removed from tblEmployeeOvertimes
and the value added to Total Overtime Hours in tblBasicInfo needs to be
deducted based on the amount added.

FYI: In the tblEmployeeOvertimes, I have a field, Actual_Hours, that stores
the amount of hours incremented; so this can be utilized in the calculation
to subtract from the field in tblBasicInfo, Total_OT Hours.

Whew! Now that I've bored to to tears...
Do you think there is hope to develop this feature...?
I think I have the methodology but not the skilss for the SQL staement(s) to
execute on the DB.

Any direction appreciated!
Jim

 

[Jeff Boyce]

Jim

OK, I think I have a better idea of the approach you chose.

I don't know if this simplifies, complicates, or is irrelevant...

Your tblBasicInfo sounds like it describes your employees (I suppose another
name for the table could be tblEmployee). And then you add in a
"calculated" field that holds an ever-changing TotalOverTimeHours value. But
if this value is based on whether or not an Employee accepts an Overtime
slot, is it really a "Total"?

I don't quite get an Overtime slot yet. Is this an opportunity, or a
mandatory situation? Your tblOvertime "stores the Overtime records" ... but
what's included in an overtime record?

By your design description, an Overtime slot can have zero, one or many
Employees associated... or do you mean only one employee can take an
overtime slot, but can take that slot more than one time? Is Overtime a "I
need someone on March 10th from 10pm to midnite", or a "I have 27 hours of
additional work, who wants a piece?"

I'm still wondering if you couldn't use a totals query to sum up detail
records for a total ... but if you count refusal to work OT as OT hours
(?!), then I'm confused what you mean by "total".

(Still trying to visualize your situation...)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks for the reply Jeff!

I'll attempt to briefly explain:

tblBasicInfo - stores static info about each employee except Total Over Time
hours, this number increases depending on accepting an overtime slot, denying
an OT slot OR if an employee cannot be reached. This creates a chronological
order of employees based on their Total Overtime Hours.
tblEmployeeOvertimes - stores each instance an employee works, denies or
cannot be contacted for overtime. I guess this could be called overtime
details
tblOvertime - stores the Overtime records

Methodology:
An Overtime record is added to tblOvertime,
For every record in tblOvertime, there are associated records in
tblEmployeeOvertimes. IE Three people are contacted to fill the OT, one
declines, another cannot be reached and the last person works. Each of these
three choices affects the Total Overtime Hours for each person contacted: No
Contact will only add half of the overtime hours to the total whereas, denied
and worked will increase by the actual number of overtime hours to be worked.

Hopefully, this paints a fairly clear picture...

Now to my delima:
End users click a button that will create a record in tblEmployeeOvertimes
and increment the field Total Overtime Hours in the tblBasicInfo... Well this
person may not qualify for the Overtime and should have never been contacted
in the first place! The record needs to be removed from tblEmployeeOvertimes
and the value added to Total Overtime Hours in tblBasicInfo needs to be
deducted based on the amount added.

FYI: In the tblEmployeeOvertimes, I have a field, Actual_Hours, that stores
the amount of hours incremented; so this can be utilized in the calculation
to subtract from the field in tblBasicInfo, Total_OT Hours.

Whew! Now that I've bored to to tears...
Do you think there is hope to develop this feature...?
I think I have the methodology but not the skilss for the SQL staement(s) to
execute on the DB.

Any direction appreciated!
Jim

I'm not entirely clear on your data structure (tables), but it sounds like
you have one table with individual entries and another table that is only a
summary ("remove in a table ... subtract [in] another table").

If so, why? Again, I'm not there and don't know your requirements, but it
seems possible to do a Totals query on the first table and come up with the
calculated total, rather than using a second table.


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[SodaJim]

Hello Jeff,

I've made comments below where appropriate...
I really appreciate your time and efforts with this issue!

Jim

OK, I think I have a better idea of the approach you chose.

I don't know if this simplifies, complicates, or is irrelevant...

Your tblBasicInfo sounds like it describes your employees (I suppose another
name for the table could be tblEmployee). And then you add in a
"calculated" field that holds an ever-changing TotalOverTimeHours value. But
if this value is based on whether or not an Employee accepts an Overtime
slot, is it really a "Total"?

Correct for the table info/naming; however, the Total_OT_Hours field is
utilized to chronologically ascend the order of the employees to show who is
to be called/contacted first. A seniority list if you will...

I don't quite get an Overtime slot yet. Is this an opportunity, or a
mandatory situation? Your tblOvertime "stores the Overtime records" ... but
what's included in an overtime record?

Yes, an Overtime slot is an opportunity for employees to work OT.
Table contains:
OT_ID - autonumber
OT_Date - date of the Overtime
OT_Hours - The actual number of hours needed to be worked. Can vary, 1 - 24
hrs.
OT_LastName - Last Name of the person working this OT.
OT_Approval - Last Name of the Officer approving the OT

By your design description, an Overtime slot can have zero, one or many
Employees associated... or do you mean only one employee can take an
overtime slot, but can take that slot more than one time? Is Overtime a "I
need someone on March 10th from 10pm to midnite", or a "I have 27 hours of
additional work, who wants a piece?"

Each Overtime slot can have more than one employee associated with it due to
the explanation below:

Overtime slots in our organization, Fire Rescue, are as follows:
Someone calls in sick, this person has certain skills, Lieutenant, Driver,
Paramedic etc... Battalion Chief(BC) will look at a report generated by the
DB in ascending order by Total_OT_Hours. This report also displays last/first
names and skill sets for each employee. So..., the BC needs a shift of OT (24
hrs) for the position of Lieutentant. BC will review the list and contact
each employee that meets the skill set until someone accepts the OT. Some
employees will be marked as "No Contact", while others may "Deny" the OT.
FYI: "No Contact" employees only have half the OT_Hours added to their
Total_OT_Hours, 12 hrs in this senario; whereas, employees that Deny or Work
will be incremented the OT_Hours, 24 in this senario. This calculation
refreshes and re-orders the list in ascending order by Total_OT_Hours so that
people get an unbiased selection for Overtime. Well, anything can be
subjective if there's human intervention...

I'm still wondering if you couldn't use a totals query to sum up detail
records for a total ... but if you count refusal to work OT as OT hours
(?!), then I'm confused what you mean by "total".

(Still trying to visualize your situation...)

We're using Total_OT_Hours stored in the tblBasicInfo (renamed tblEmployees)
as a way to order the employees so that employees will be afforded the same
opportunity to work OT. An employee that works an OT slot will be placed
lower in the list, thus when a new OT slot is presented, this employee may
not be contacted. Should everyone above this employee on the list "deny" or
"cannot be contacted", then this employee may be contacted for this instance
of OT.

If I wasn't having trouble figuring why this DB is so large (10 megs and
posted in another thread) and you accepted the sending of DB's, I would send
this to you for quick review... I'm new at this discussion area and don't
know accepted practices. I do understand your position and as I have stated
previously, I appreciate your time and efforts, so me sending this DB may be
out of the question.

Any additional input is appreciated greatly!
Jim

 

[Jeff Boyce]

See my comments in-line w/ your comments ...

Hello Jeff,

I've made comments below where appropriate...
I really appreciate your time and efforts with this issue!



Correct for the table info/naming; however, the Total_OT_Hours field is
utilized to chronologically ascend the order of the employees to show who is
to be called/contacted first. A seniority list if you will...

So your field named [Total_OT_Hours] doesn't really have actual hours? I
don't understand "chronologically ascend" -- and if the field actually holds
total hours, you're using it to sort who has the fewest?

Yes, an Overtime slot is an opportunity for employees to work OT.
Table contains:
OT_ID - autonumber
OT_Date - date of the Overtime
OT_Hours - The actual number of hours needed to be worked. Can vary, 1 - 24
hrs.

So, if your [Overtime] table actually holds the opportunity for overtime...

OT_LastName - Last Name of the person working this OT.
OT_Approval - Last Name of the Officer approving the OT

.... you may not want to connect the person working the OT to the opportunity
in this table. I can see keeping the Approver of the OT here, unless the
Approver is approving the person working the OT, rather than authorizing
"nn" hours of OT.

(And by the way, using the LastName of a person is a risky way to store
data. I'm guessing you don't have two folks with the last name of "Smith"
or "Jones" or "Williams", but what happens when you do?! Instead, consider
a table of people, with an ID for each, and storing just the IDs when you
need to know which person is connected to the record in the table.

Each Overtime slot can have more than one employee associated with it due to
the explanation below:

Then you absolutely do not want to put the OT_LastName in the Overtime
(opportunity) table, if more than one person can work the OT (slot).

Overtime slots in our organization, Fire Rescue, are as follows:
Someone calls in sick, this person has certain skills, Lieutenant, Driver,
Paramedic etc... Battalion Chief(BC) will look at a report generated by the
DB in ascending order by Total_OT_Hours. This report also displays last/first
names and skill sets for each employee. So..., the BC needs a shift of OT (24
hrs) for the position of Lieutentant. BC will review the list and contact
each employee that meets the skill set until someone accepts the OT. Some
employees will be marked as "No Contact", while others may "Deny" the OT.
FYI: "No Contact" employees only have half the OT_Hours added to their
Total_OT_Hours, 12 hrs in this senario; whereas, employees that Deny or Work
will be incremented the OT_Hours, 24 in this senario. This calculation
refreshes and re-orders the list in ascending order by Total_OT_Hours so that
people get an unbiased selection for Overtime. Well, anything can be
subjective if there's human intervention...

So the Overtime opportunity is specific to a category of qualifications, or
to a set of (selected) qualifications? How do you match the person's
qualifications to the OT opportunity?

And your [Total_OT_Hours] field gets incremented for someone who turns down
the OT opportunity?

As I responded before, rather than continually updating this ?totals? field,
you'd be better off using a query to add up actual hours work (are you
keeping this somewhere else?), and using that query to help you select who
is eligible.

We're using Total_OT_Hours stored in the tblBasicInfo (renamed tblEmployees)
as a way to order the employees so that employees will be afforded the same
opportunity to work OT. An employee that works an OT slot will be placed
lower in the list, thus when a new OT slot is presented, this employee may
not be contacted. Should everyone above this employee on the list "deny" or
"cannot be contacted", then this employee may be contacted for this instance
of OT.

If you had a query that summed all actual and signed-up-for hours for each
person, then, by category, sorted it in ascending hours, the person at the
top in each category would be first contacted, right?

The reason I'm trying to steer you away from the [Total_OT_Hours] field in
your table is because it creates the kinds of synchronization problems
you're experiencing. A dynamic query will always take into account all
available information, and requires no updating, synchronizing, removing,
etc.

If someone signs up for a slot, then can't make it or isn't qualified,
removing that sign up means the hours don't show in the query. Or, if you
want to track who signs up and then backs out, you can use a Yes/No field to
mark "Cancelled", and exclude those from your query.

If I wasn't having trouble figuring why this DB is so large (10 megs and
posted in another thread) and you accepted the sending of DB's, I would send
this to you for quick review... I'm new at this discussion area and don't
know accepted practices. I do understand your position and as I have stated
previously, I appreciate your time and efforts, so me sending this DB may be
out of the question.

If your DB has grown, perhaps you could Compact & Repair it -- but ONLY
after you make a backup copy. You ARE keeping backups, right?!

If you sent me your DB I'd have to assume you were hiring me, and bill you
at my customary rate. Let's see if we can get it worked out in the
newsgroup...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/