Timesheet

[jmq]

Finally! with all your help, i am able to build a piece of it.

Would you mind taking a peak of it? for improvements?
for a better world?

PS:
Issues i still can't resolve:

1. I want to set the focus to txtMon1 after i click on date picker of txtStart
2. How to elegantly minimise the code in cmdSubmit button - Using Loop
3. How to elegantly calculate all the fields using, modules? anything that minimise coding
4. Should we use SQL Strings?
5. What recordset to use properly or professionaly? DAO etc..

Thank you guys!

[plog]

tbl1Timesheet isn't properly structured.

~When you start storing specific values in field names its time for different set up. Same with numbering field names.

~you aren't using the primary key (ID) from tbl1Employees properly--or at all. It is to be the foreign key in tbl1Timesheets not EmpNo.

~don't name every primary key 'ID', prefix it with relation to the table it is in otherwise you will end up with a ton of ID fields when you run queries and not know which one you are working with.

tblTimeSheet should be structured like so:

ts_ID, autonumber, primary key
ID_employee, number, links to tbl1Employees.ID
WorkDate, date, will hold date the work was done on
WorkAmount, number will hold the value that is worked

That's it, just 4 fields. Then, when you want to put 2 weeks of work in for an employee you would submit 14 records, not 1 record with 14 fields of data.

[moke123]

what Plog said and I would also consider a field "PayType" and have a lookup table with Pay Types such as Regular Pay, Vacation Pay, Sick Time, etc. This would enable dealing with those types of variables.

[jmq]

@Plog: I don't understand this

That's it, just 4 fields. Then, when you want to put 2 weeks of work in for an employee you would submit 14 records, not 1 record with 14 fields of data.

[Uncle Gizmo]

Originally Posted by jmq

@Plog: I don't understand this

What plog is saying is that you have a design issues. In other words, the way you have constructed your database will lead you to serious problems if you carry on.

Please see my blog for more information. There is text, pictures and videos explaining in detail the issue and the solution...

Excel in Access

[plog]

Tables should accomodate data vertically (with rows) not horizontally (with columns). tblTimesheet has too many column. Currently, 1 record in tblTimesheet equals 14 days of data.

The correct way the table should be set up is so that 1 record equals 1 day. So when you want to put in 14 days of data you don't add one record like you are now, but instead you add 14 records.

[jmq]

@plog:
now i understand this.

ts_ID, autonumber, primary key
ID_employee, number, links to tbl1Employees.ID
WorkDate, date, will hold date the work was done on
WorkAmount, number will hold the value that is worked