Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-08-2017, 09:22 PM   #1
jmq
Newly Registered User
 
Join Date: Oct 2017
Posts: 45
Thanks: 18
Thanked 0 Times in 0 Posts
jmq is on a distinguished road
Lightbulb Timesheet

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!
Attached Files
File Type: zip FuncXTimeSheet.zip (131.5 KB, 21 views)

jmq is offline   Reply With Quote
Old 10-09-2017, 05:08 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,118
Thanks: 10
Thanked 1,952 Times in 1,913 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Timesheet

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jmq (10-09-2017)
Old 10-09-2017, 05:38 AM   #3
moke123
Newly Registered User
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 431
Thanks: 0
Thanked 133 Times in 126 Posts
moke123 is on a distinguished road
Re: Timesheet

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.

moke123 is offline   Reply With Quote
Old 10-09-2017, 03:00 PM   #4
jmq
Newly Registered User
 
Join Date: Oct 2017
Posts: 45
Thanks: 18
Thanked 0 Times in 0 Posts
jmq is on a distinguished road
Re: Timesheet

@Plog: I don't understand this
Quote:
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.
jmq is offline   Reply With Quote
Old 10-09-2017, 03:12 PM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 8,738
Thanks: 257
Thanked 661 Times in 632 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
Send a message via Skype™ to Uncle Gizmo
Re: Timesheet

Quote:
Originally Posted by jmq View Post
@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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
............

Join My YouTube Channel ---
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Hundereds of MS Access Videos!!!!!
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
jmq (10-09-2017)
Old 10-10-2017, 04:36 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,118
Thanks: 10
Thanked 1,952 Times in 1,913 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Timesheet

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jmq (10-10-2017)
Old 10-10-2017, 02:27 PM   #7
jmq
Newly Registered User
 
Join Date: Oct 2017
Posts: 45
Thanks: 18
Thanked 0 Times in 0 Posts
jmq is on a distinguished road
Re: Timesheet

@plog:
now i understand this.
Quote:
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


jmq is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Timesheet mickelingon Forms 1 02-07-2008 06:21 AM
timesheet table to create popup if timesheet entries is not entered day before hillsee General 4 05-24-2007 07:55 AM
Timesheet biggcc General 6 11-27-2006 04:18 PM
Timesheet tigerstripes Forms 2 03-25-2005 07:22 AM




All times are GMT -8. The time now is 06:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World