Running Sum for a "Session" of hours entry. 1

[Vyurr]

Hello all,

I have a DB that I use to track overtime hours worked by employees. The hours are sent to me from different departments in an excel spreadsheet form. I then manually key the hours into the database (I realize that I could probably import the spreadsheets as opposed to rekeying, but I prefer doing it manually).

The hours are keyed into a continuous subform of a main Hours entry form. In order to check the data after I key it, I run a query segregating the hours for the previous week and match the total to the totals on the spreadsheets.

As I key all of the hours in one "sitting" so to speak, I'm wondering if there is a way to use a textbox on the mainform that will pull the # of hours entered and give me a running sum as I key them. This would just save me from adjusting a running a query after each keying "session".

So, (hopefully to clarify) the next time I load the form, the textbox would read "0" and then would sum all the entries until the next time the form is loaded.

I hope what I am trying to accomplish is clear. I can provide a table structure if needed, but I can tell you that the data is fully normalized.

Thanks in advance,

D

 

[kjv1611]

Although I think we may need more detail, what you want to do is definitely possible. I think there would be at least 2 ways of doing this, actually.

To clarify further, are you wanting a separate textbox that shows the running sub-total, or just using one textbox for entering hours, and then hit <Enter> and that same textbox give you a sub-total?

I'd personally recommend a separate one in case you are in doubt of the totals. Possibly even a few boxes that will list the details (or a sub-form) and then one text box separate that shows the sub-total (other than where you are entering the data).

Also, I'm pretty sure this part:
==> (I realize that I could probably import the spreadsheets as opposed to rekeying, but I prefer doing it manually).

Really should say:
(I realize that I [highlight]should[/highlight] import the spreadsheets as opposed to rekeying, but I prefer doing it manually).

The reason I say this is that you have 2 manual processes doing the same thing. That's bad on 2 counts:
[ol][li]Reduced overall productivity[/li]
[li]Increased chance of human error - where most of the errors come from. Even the "technical" errors are typically caused by a person. We are all prone to errors.[/li][/ol]

--

"If to err is human, then I must be some kind of human!" -Me

 

[Vyurr]

Hey kvj1611,

Thanks for the quick response.

I recognize the inefficiency of rekeying data, but (without going into too much detail), in this case there is some analysis required as the data is keyed, so I'd have to look at each entry whether I keyed it myself or not.

As for the text box, what I would prefer is a separate text box that will give me a subtotal that updates as I key. The continuous subform that I key the hours into requires a date field and thus has all of the previous historic entries listed as well, so a running sum in that form wouldn't be useful to me, as it would sum the entire fiscal year as opposed to just this week's entries (which is what I am trying to accomplish).

Like I said, I am checking my totals against a query I've designed, its just the extra step of having to adjust the dates in the query to match the previous week to isolate the hours I am trying to total.

Just to hopefully clarify, here is the relevant table:

tblHours

EmpID (employee ID - FK to tblEmployees)
CostCentre (where the time was charged)
Hours
Date
PayOrComp (employees can take leave time in lieu of pay)
Meal (whether or not they qualify for a meal allowance)
RecordID (Autonumber PK)

Salary information is also kept in a seperate table containting effective dates (if that matters here).

If there is anything else i can do to clarify, please don't hesitate to ask

D

 

[davejam]

if you just want something that totals as you enter you could have an unbound field in say the header of the form called txtTotal default 0, type number (decimal??)

then with your entry box add an afterupdate with the code that is as follows

private sub myfield_afterupdate()
me.txtTotal = txtTotal + me.myfield
end sub

you could then obviously embellish on this with errors etc like

private sub myfield_afterupdate()
if isnull(me.myfield) or me.myfield = "" then exit sub
me.txtTotal = txtTotal + me.myfield
end sub


as i say, think this might work, or at least with some jiggery pokery!!

daveJam

even my shrink says its all your f#@/ing fault

 

[Vyurr]

Well done sir. Thank you much...works like a charm.

D