First off.. thanks are again in order. Again I truly appreciate your time and expertise.
Welcome
Secondly, I may have jumped into a project that is a bit over my head and have realized that I do not have a grasp on database programming as I once thought I had.
By the end, that will change
I initially thought was going to be a piece of cake!
Every time i'm asked to estimate the amount of time taken for some work to be done, I multiply my initial desired response by 3, 5 if I'm using new technology. I foolishly said I could get a major financial management system done in 3 months, forgetting that I was new to VB.NET 2.0 and I should have said 5. As it is, we are 6 months down the line..
And some of it has been a pig; I seem to get all the questions to which noone has an answer
My application currently has 1 database, Employees and Hours. They have a one to many relationship which I have set up in the msAccess database where Employee.EmpNum is primary key. In the Hours table, I have an autoNumber field set as primary key as the rest of the fields would not create a unique row.
I disagree here. I think the employee and the TimeIn DO create a unique row, if each employee can technically only clock in once per day. If you would allow multiple clockins and clockouts then I would simply have a row
That had InDate, OutDate, Task
I could then use database functions to sum them, and an employees working day might be:
empID, inDate, outDate, purpose
JON, 01/01/1901 09:00, 01/01/1901 12:00, morning work
JON, 01/01/1901 12:00, 01/01/1901 13:00, lunch
JON, 01/01/1901 09:00, , afternoon work
jons afternoon clockout is null because he is still at work. If he clocked out we update the record:
UPDATE work SET outDate = Now() WHERE empID = 'JON' and OutDate IS NULL
if he clocks out but intends to return we ask him what he is doing (lunch? doc appoint?):
UPDATE work SET outDate = Now() WHERE empID = 'JON' and OutDate IS NULL;
INSERT INTO work VALUES('JON', Now(), NULL, 'doctor appointment);
that insert statement would be used to clock him in in the morning too. .so really our system relies on only 2 statements to record peoples whereabouts
My application successfully updates the Hours table when the user clicks the appropriate In, LunchOut, LunchIn, and Out buttons. When the Out button is clicked, it calculates the total hours for the day and updates the TotalHours field in the Hours table. That all works fine now.
The important point of note in any system here is that there is no need to store the total hours - you know the start, you know the end, you can run this query (against my table, imagine yours to be similar):
SELECT empID, inDate, (outDate-inDate) * 24 as HoursOnTask, purpose WHERE outDate IS NOT NULL
We'd get for my data before:
JON, 01/01/1901, 3, morning work
JON, 01/01/1901, 1,lunch
We could group these. Suppose we got the records for just this month:
SELECT
empID,
(outDate-inDate) * 24 as HoursOnTask,
purpose
FROM
work
WHERE
outDate IS NOT NULL and
inDate between [I]TODAY-10[/I] AND [I]TODAY[/I]
GROUP BY
empID, purpose
We will leave the words in italics as an excercie for later; im, not bothered about data ranges right now.
Suppsoe jon had worked 10 mornings at 3 hours and lunched 10 times and worked 5 hours in the afternoon for 10 afternoons. we'd get this from our query:
JON, 30, morning work
JON, 10, lunch
JON, 50, afternoon work
If we dumped the purpose column off the sheet we's just get:
JON, 90
90 hours in 10 days
Ok, with that out of the way, I am now to the point where I want to total all the hours for each employee for the 2 week pay period and this is where I planned on using arrays.
Then have the database do it.. select all the records for the last 2 weeks, group them by employeeid, sum up the hours worked, hours lunched etc
you can even join in another table that tells us for each empID what they get paid per hour for each. You can have the database retrun you columns like overtime etc:
SELECT
empID
CASE WHEN totalHoursWorked > 40 THEN
40
ELSE
totalHoursWorked
END as normalHours,
CASE WHEN totalHoursWorked > 40 THEN
totalHoursWorked - 40
ELSE
0
END as overtimeHours
FROM(
SELECT empID, sum(outTime - inTime) as totalHoursWorked
WHERE
[I]date_range_in_last_2_weeks[/I]
GROUP BY empID
)
If jon worked a cumulative of 57 hours in the last 2 weeks we get:
JON, 40, 17
I planned on doing this (because my lack of experience does not know of any other way). The following is not tested code, as I know it will not be correct way of doing things, but will give you my ideas on how I was going to do it.
Get the database to do as much donkeywork as possible, it saves a lot of messing and keeps your client side nice and clean
My plans were to use these arrays to create my sql statements. My code would go something like this:
I think you need to broaden your awareness of what you can get a database to do - they dont just store data, but they manipulate it, sum it up, sort it and restrict it..
There is more involved there, like calculating overtime after 40 hours for each week for each employee, etc. but you get the idea of what I planned to do.
I do.. and hopefully you can read my SQLs and go "holy cow.. it would have taken me lines and lines of code to reinvent that add-the-numbers-up wheel and cjard did it in about 45 seconds in this post!"
so, get way deeper into the SQL; it can help you soo much
I them am going to create an excel spreadsheet with a bunch of info that will be uploaded to another payroll company that does our payroll (writes checks, etc) in their desired format.
I'd dump it to a CSV file - excel can open it and its easier to write opne of those than create an XLS file..
Really appreciate your help!
No problems..