If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Subforms?
Help me, please! I am putting together an automated Time Card Entry Form. I
have the following fields on this form as follows: 1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 2. Employee Number: combo box that will populate next field, Employee Name, after user select correct Employee Number. 3. Employee Name: Text Box will appear "LastName, FirstName" order. 4. Description: combo box containing a list of labor description that has associated fields as follows. 5. Cost Center: Text Box will populate after selecting Description. 6. Acct: Text Box will populate after selecting Description. 7. Category: Text Box will populate after selecting Description. 8. Pay Type: Combo Box to eliminate data entry errors. 9. Allocation: Free form, to type comments. 10. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri 11. Regular: totals of regular hours worked. 12. Overtime: totals of overtime hours worked. 13. Vacation: totals of vacation hours taken. 14. Holiday: totals of holiday hours taken. 15. Sick: totals of sick hours taken. 16. Personal: totals of personal hours taken. 17. Total Wk Hrs: total hours for the week for per line of Description. Here are my tables: 1. tblEmployees: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number (Primary Key) 4) EmployeeClass = Text 5) EmployeeType = Text 2. tblAccounts: 1) CostCenter = Number 2) AcctNo = Number 3) Category = Text 4) AcctName = Text 5) AcctDescription = Text (Primary Key) 6) PayType = Text 3. tblPayType: 1) PayType = Text 2) Description = Text 4. tblRegRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) HourlyRate = Number 5) AnnualRate = Number 5. tblOtRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) OTHourlyRate = Number 6) AnnualRate = Number 6. tblDdRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) DDHourlyRate = Number 6) AnnualRate = Number 7. tblTimecard: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number 4) PayPeriod = Date/Time 5) EmployeeClass = Text 6) EmployeeType = Text 7) SAT = Number 8) SUN = Number 9) MON = Number 10) TUE = Number 11) WED = Number 12) THU = Number 13) FRI = Number 14) CostCenter = Number 15) AcctNo = Number 16) Category = Text 17) AcctName = Text 18) AcctDescription = Text 19) PayType = Text 20) Allocation = Text 21) TotalRegHrs = Number 22) TotalOTHrs = Number 23) TotalVacHrs = Number 24) TotalHolHrs = Number 25) TotalSickHrs = Number 26) TotalPersHrs = Number 27) TotalWeekHrs = Numbers 8. tblPayrollSchedule: 1) PayId = Number 2) PayStartDate = Date/Time 3) PayEndDate = Date/Time 4) CheckDate = Date/Time Here is the layout of my form in this order: 1. Week Ending 2. Employee Number 3. Employee Last Name 4. Employee First Name 5. Description 6. Cost Center 7. Acct 8. Category 9. Pay Type 10. Allocation 11. SAT 12. SUN 13. MON 14. TUE 15. WED 16. THU 17. FRI 18. Total Wk Hrs Question 1: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Question 2: how come the following fields are not populating: 1. LastName 2. FirstName 3. EmployeeClass 4. EmployeeType 5. CostCenter 6. AcctNo 7. Category 8. AcctName But these fields are populating: 1. EmployeeId 2. PayPeriod 3. AcctDescription 4. PayType 5. Allocation 6. TotalRegHrs 7. TotalOTHrs 8. TotalVacHrs 9. TotalHolHrs 10. TotalSickHrs 11. TotalPersHrs Question 3: how can I get the 2nd section which starts from "5. Description" til "18. Total Wk Hrs" to display in multiple rows like a Datasheet format? Do I need to create a subform? But "Description" field still needs to have a combo box for user to pick from a list of labor description that will automatically populate these fields: 1. Cost Center 2. Acct 3. Category Question 4: If I do need to create subform then how can I link the mainform to subform and what foreign keys do I need and where do I put these foreign keys? Question 5: what formula do I use to calculate "Total Wk Hrs" field? Do I need to create an actual field in tblTimecard or can I just get the total from summing with an Expression? It's been a while since I have done Access development. Sincerely, AccessRookie =) |
#2
|
|||
|
|||
AccessRookie,
I am not really able to specifically answer your questions. But I would comment that your table design at the moment involves a lot of redundancy (i.e. the same information being stored in more than one place), the storage of derived/calculated data (which makes things more difficult and unnecessarily complicated), and other examples of unnormalisation (for example the "fields as data" trap of separate fields for the days of the week). Is there a chance you could review your table structure? -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: Help me, please! I am putting together an automated Time Card Entry Form. I have the following fields on this form as follows: 1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 2. Employee Number: combo box that will populate next field, Employee Name, after user select correct Employee Number. 3. Employee Name: Text Box will appear "LastName, FirstName" order. 4. Description: combo box containing a list of labor description that has associated fields as follows. 5. Cost Center: Text Box will populate after selecting Description. 6. Acct: Text Box will populate after selecting Description. 7. Category: Text Box will populate after selecting Description. 8. Pay Type: Combo Box to eliminate data entry errors. 9. Allocation: Free form, to type comments. 10. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri 11. Regular: totals of regular hours worked. 12. Overtime: totals of overtime hours worked. 13. Vacation: totals of vacation hours taken. 14. Holiday: totals of holiday hours taken. 15. Sick: totals of sick hours taken. 16. Personal: totals of personal hours taken. 17. Total Wk Hrs: total hours for the week for per line of Description. Here are my tables: 1. tblEmployees: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number (Primary Key) 4) EmployeeClass = Text 5) EmployeeType = Text 2. tblAccounts: 1) CostCenter = Number 2) AcctNo = Number 3) Category = Text 4) AcctName = Text 5) AcctDescription = Text (Primary Key) 6) PayType = Text 3. tblPayType: 1) PayType = Text 2) Description = Text 4. tblRegRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) HourlyRate = Number 5) AnnualRate = Number 5. tblOtRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) OTHourlyRate = Number 6) AnnualRate = Number 6. tblDdRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) DDHourlyRate = Number 6) AnnualRate = Number 7. tblTimecard: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number 4) PayPeriod = Date/Time 5) EmployeeClass = Text 6) EmployeeType = Text 7) SAT = Number 8) SUN = Number 9) MON = Number 10) TUE = Number 11) WED = Number 12) THU = Number 13) FRI = Number 14) CostCenter = Number 15) AcctNo = Number 16) Category = Text 17) AcctName = Text 18) AcctDescription = Text 19) PayType = Text 20) Allocation = Text 21) TotalRegHrs = Number 22) TotalOTHrs = Number 23) TotalVacHrs = Number 24) TotalHolHrs = Number 25) TotalSickHrs = Number 26) TotalPersHrs = Number 27) TotalWeekHrs = Numbers 8. tblPayrollSchedule: 1) PayId = Number 2) PayStartDate = Date/Time 3) PayEndDate = Date/Time 4) CheckDate = Date/Time Here is the layout of my form in this order: 1. Week Ending 2. Employee Number 3. Employee Last Name 4. Employee First Name 5. Description 6. Cost Center 7. Acct 8. Category 9. Pay Type 10. Allocation 11. SAT 12. SUN 13. MON 14. TUE 15. WED 16. THU 17. FRI 18. Total Wk Hrs Question 1: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Question 2: how come the following fields are not populating: 1. LastName 2. FirstName 3. EmployeeClass 4. EmployeeType 5. CostCenter 6. AcctNo 7. Category 8. AcctName But these fields are populating: 1. EmployeeId 2. PayPeriod 3. AcctDescription 4. PayType 5. Allocation 6. TotalRegHrs 7. TotalOTHrs 8. TotalVacHrs 9. TotalHolHrs 10. TotalSickHrs 11. TotalPersHrs Question 3: how can I get the 2nd section which starts from "5. Description" til "18. Total Wk Hrs" to display in multiple rows like a Datasheet format? Do I need to create a subform? But "Description" field still needs to have a combo box for user to pick from a list of labor description that will automatically populate these fields: 1. Cost Center 2. Acct 3. Category Question 4: If I do need to create subform then how can I link the mainform to subform and what foreign keys do I need and where do I put these foreign keys? Question 5: what formula do I use to calculate "Total Wk Hrs" field? Do I need to create an actual field in tblTimecard or can I just get the total from summing with an Expression? It's been a while since I have done Access development. Sincerely, AccessRookie =) |
#3
|
|||
|
|||
Thanks, Steve for your suggestion. Been busy with other projects to reply
back. With your suggestion, I reviewed table structure and design. Initially, I started out with 4 tables: 1. tblAccounts 2. tblEmployees 3. tblPayType 4. tblTimecard As a result of following documentation on Database Normalization Basics from this site and another site: http://www.utteraccess.com/forums/pr...3208&type=post I restructured the tables and renamed more descriptive and reviewed table relationships. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayroll Schedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id 2. Week Ending 3. Employee Number 4. Employee Last Name 5. Employee First Name Here is the layout of Subform in this order: 1. Acct Id 2. Description 3. Cost Center 4. Acct 5. Category 6. Pay Type Id 7. Pay Type 8. Allocation 9. SAT 10. SUN 11. MON 12. TUE 13. WED 14. THU 15. FRI 16. Wk Hrs I hope this posting will help others facing same issue as me. AccessRookie =) "Steve Schapel" wrote: AccessRookie, I am not really able to specifically answer your questions. But I would comment that your table design at the moment involves a lot of redundancy (i.e. the same information being stored in more than one place), the storage of derived/calculated data (which makes things more difficult and unnecessarily complicated), and other examples of unnormalisation (for example the "fields as data" trap of separate fields for the days of the week). Is there a chance you could review your table structure? -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: Help me, please! I am putting together an automated Time Card Entry Form. I have the following fields on this form as follows: 1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 2. Employee Number: combo box that will populate next field, Employee Name, after user select correct Employee Number. 3. Employee Name: Text Box will appear "LastName, FirstName" order. 4. Description: combo box containing a list of labor description that has associated fields as follows. 5. Cost Center: Text Box will populate after selecting Description. 6. Acct: Text Box will populate after selecting Description. 7. Category: Text Box will populate after selecting Description. 8. Pay Type: Combo Box to eliminate data entry errors. 9. Allocation: Free form, to type comments. 10. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri 11. Regular: totals of regular hours worked. 12. Overtime: totals of overtime hours worked. 13. Vacation: totals of vacation hours taken. 14. Holiday: totals of holiday hours taken. 15. Sick: totals of sick hours taken. 16. Personal: totals of personal hours taken. 17. Total Wk Hrs: total hours for the week for per line of Description. Here are my tables: 1. tblEmployees: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number (Primary Key) 4) EmployeeClass = Text 5) EmployeeType = Text 2. tblAccounts: 1) CostCenter = Number 2) AcctNo = Number 3) Category = Text 4) AcctName = Text 5) AcctDescription = Text (Primary Key) 6) PayType = Text 3. tblPayType: 1) PayType = Text 2) Description = Text 4. tblRegRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) HourlyRate = Number 5) AnnualRate = Number 5. tblOtRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) OTHourlyRate = Number 6) AnnualRate = Number 6. tblDdRate: 1) EmployeeId = Number (Primary Key) 2) EmployeeType = Text 3) PayType = Text 4) RegHourlyRate = Number 5) DDHourlyRate = Number 6) AnnualRate = Number 7. tblTimecard: 1) LastName = Text 2) FirstName = Text 3) EmployeeId = Number 4) PayPeriod = Date/Time 5) EmployeeClass = Text 6) EmployeeType = Text 7) SAT = Number 8) SUN = Number 9) MON = Number 10) TUE = Number 11) WED = Number 12) THU = Number 13) FRI = Number 14) CostCenter = Number 15) AcctNo = Number 16) Category = Text 17) AcctName = Text 18) AcctDescription = Text 19) PayType = Text 20) Allocation = Text 21) TotalRegHrs = Number 22) TotalOTHrs = Number 23) TotalVacHrs = Number 24) TotalHolHrs = Number 25) TotalSickHrs = Number 26) TotalPersHrs = Number 27) TotalWeekHrs = Numbers 8. tblPayrollSchedule: 1) PayId = Number 2) PayStartDate = Date/Time 3) PayEndDate = Date/Time 4) CheckDate = Date/Time Here is the layout of my form in this order: 1. Week Ending 2. Employee Number 3. Employee Last Name 4. Employee First Name 5. Description 6. Cost Center 7. Acct 8. Category 9. Pay Type 10. Allocation 11. SAT 12. SUN 13. MON 14. TUE 15. WED 16. THU 17. FRI 18. Total Wk Hrs Question 1: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Question 2: how come the following fields are not populating: 1. LastName 2. FirstName 3. EmployeeClass 4. EmployeeType 5. CostCenter 6. AcctNo 7. Category 8. AcctName But these fields are populating: 1. EmployeeId 2. PayPeriod 3. AcctDescription 4. PayType 5. Allocation 6. TotalRegHrs 7. TotalOTHrs 8. TotalVacHrs 9. TotalHolHrs 10. TotalSickHrs 11. TotalPersHrs Question 3: how can I get the 2nd section which starts from "5. Description" til "18. Total Wk Hrs" to display in multiple rows like a Datasheet format? Do I need to create a subform? But "Description" field still needs to have a combo box for user to pick from a list of labor description that will automatically populate these fields: 1. Cost Center 2. Acct 3. Category Question 4: If I do need to create subform then how can I link the mainform to subform and what foreign keys do I need and where do I put these foreign keys? Question 5: what formula do I use to calculate "Total Wk Hrs" field? Do I need to create an actual field in tblTimecard or can I just get the total from summing with an Expression? It's been a while since I have done Access development. Sincerely, AccessRookie =) |
#4
|
|||
|
|||
AccessRookie,
Thanks for getting back, and congratulations on the good work and the progress you have made. The biggest change I would make (and recommend) to what you have now got, is in the intSat, intSun, intMon, etc fields in the tblTimecardHours table. Whatever the data in these fields, it should all be in only one field, with a separate record for each entry, and if necessary a date and a type field. -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: Thanks, Steve for your suggestion. Been busy with other projects to reply back. With your suggestion, I reviewed table structure and design. Initially, I started out with 4 tables: 1. tblAccounts 2. tblEmployees 3. tblPayType 4. tblTimecard As a result of following documentation on Database Normalization Basics from this site and another site: http://www.utteraccess.com/forums/pr...3208&type=post I restructured the tables and renamed more descriptive and reviewed table relationships. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayroll Schedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id 2. Week Ending 3. Employee Number 4. Employee Last Name 5. Employee First Name Here is the layout of Subform in this order: 1. Acct Id 2. Description 3. Cost Center 4. Acct 5. Category 6. Pay Type Id 7. Pay Type 8. Allocation 9. SAT 10. SUN 11. MON 12. TUE 13. WED 14. THU 15. FRI 16. Wk Hrs I hope this posting will help others facing same issue as me. AccessRookie =) |
#5
|
|||
|
|||
No, that's not a good suggestion because, I have not posted the format of how
the timesheet looks like, here is the detailed part: Acct Name Acct # SAT SUN MON TUE WED THU FRI Vaction 1111.1000 8 8 8 8 8 Each timecard detail has the specified labor allocated to each account number for the total hours of each day per labor expense. Therefore, it doesn't make sense in my situation to make it one field. I know I have not totally described how the timesheet look like in detailed since I can't attached any files to this message. But now, I have another issue, please see my revised table structure on tblPayrollSchedule and new questions: elp me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayrollSchedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id -- combo box with primary key from tblPayrollSchedule and payroll date. 2. Week Ending -- automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 3. Employee Number -- combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number. 4. Employee Last Name -- automatically populates when Employee Number is selected. 5. Employee First Name -- automatically populates when Employee Number is selected. Here is the layout of Subform in this order: 1. Acct Id -- combo box containing a list of labor description that has associated fields as follows. 2. Acct Name -- Text Box will populate after selecting Acct Id. 3. Cost Center -- Text Box will populate after selecting Acct Id. 4. Acct -- Text Box will populate after selecting Acct Id. 5. Category -- Text Box will populate after selecting Acct Id. 6. Pay Type Id -- combo box that will populate Pay Type. 7. Pay Type -- automatically populates when Pay Type Id is selected. 8. Allocation -- Free form, to type comments. 9. SAT -- hours worked for per line of acct id on specific day. 10. SUN -- hours worked for per line of acct id on specific day. 11. MON -- hours worked for per line of acct id on specific day. 12. TUE -- hours worked for per line of acct id on specific day. 13. WED -- hours worked for per line of acct id on specific day. 14. THU -- hours worked for per line of acct id on specific day. 15. FRI -- hours worked for per line of acct id on specific day. 16. Wk Hrs -- total hours for the week for per line of Acct Id. Question 1: what code do I need to automatically populate "Week Ending" field with the correct "dtmPayEndDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending). or Create a setup form in which the user selects "week Ending" date at beginning before entering Time Card data. Then from the setup form, data gets transferred/displayed into Main Form. If so, can someone assist in writing code that passes data from one form to another form or save setup form data into its own table. Question 2: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI)? No need to store total since it is only need to display in data entry form (subform) and printing report. Help!!! Sincerely, AccessRookie =) "Steve Schapel" wrote: AccessRookie, Thanks for getting back, and congratulations on the good work and the progress you have made. The biggest change I would make (and recommend) to what you have now got, is in the intSat, intSun, intMon, etc fields in the tblTimecardHours table. Whatever the data in these fields, it should all be in only one field, with a separate record for each entry, and if necessary a date and a type field. -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: Thanks, Steve for your suggestion. Been busy with other projects to reply back. With your suggestion, I reviewed table structure and design. Initially, I started out with 4 tables: 1. tblAccounts 2. tblEmployees 3. tblPayType 4. tblTimecard As a result of following documentation on Database Normalization Basics from this site and another site: http://www.utteraccess.com/forums/pr...3208&type=post I restructured the tables and renamed more descriptive and reviewed table relationships. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayroll Schedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id 2. Week Ending 3. Employee Number 4. Employee Last Name 5. Employee First Name Here is the layout of Subform in this order: 1. Acct Id 2. Description 3. Cost Center 4. Acct 5. Category 6. Pay Type Id 7. Pay Type 8. Allocation 9. SAT 10. SUN 11. MON 12. TUE 13. WED 14. THU 15. FRI 16. Wk Hrs I hope this posting will help others facing same issue as me. AccessRookie =) |
#6
|
|||
|
|||
AccessRookie,
The structure of your data (i.e. table design) should be dictated by the nature of the data itself. Allowing your table design to be influenced by what you want your forms to look like will often prove to be a mistake. Having separate fields for each day of the week is incorrect. Re-designing your form to meet the data requirements would be a better approach. As regards your question about the dtmPayEndDate entry, what is the relationship between dtmPayStartDate and dtmPayEndDate? How does the data get entered into dtmPayStartDate? Is dtmPayEndDate always 6 days after dtmPayStartDate? If so, you probably only need one of these fields. If not, please give an example of what you want to have happen. -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: No, that's not a good suggestion because, I have not posted the format of how the timesheet looks like, here is the detailed part: Acct Name Acct # SAT SUN MON TUE WED THU FRI Vaction 1111.1000 8 8 8 8 8 Each timecard detail has the specified labor allocated to each account number for the total hours of each day per labor expense. Therefore, it doesn't make sense in my situation to make it one field. I know I have not totally described how the timesheet look like in detailed since I can't attached any files to this message. But now, I have another issue, please see my revised table structure on tblPayrollSchedule and new questions: elp me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayrollSchedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id -- combo box with primary key from tblPayrollSchedule and payroll date. 2. Week Ending -- automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 3. Employee Number -- combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number. 4. Employee Last Name -- automatically populates when Employee Number is selected. 5. Employee First Name -- automatically populates when Employee Number is selected. Here is the layout of Subform in this order: 1. Acct Id -- combo box containing a list of labor description that has associated fields as follows. 2. Acct Name -- Text Box will populate after selecting Acct Id. 3. Cost Center -- Text Box will populate after selecting Acct Id. 4. Acct -- Text Box will populate after selecting Acct Id. 5. Category -- Text Box will populate after selecting Acct Id. 6. Pay Type Id -- combo box that will populate Pay Type. 7. Pay Type -- automatically populates when Pay Type Id is selected. 8. Allocation -- Free form, to type comments. 9. SAT -- hours worked for per line of acct id on specific day. 10. SUN -- hours worked for per line of acct id on specific day. 11. MON -- hours worked for per line of acct id on specific day. 12. TUE -- hours worked for per line of acct id on specific day. 13. WED -- hours worked for per line of acct id on specific day. 14. THU -- hours worked for per line of acct id on specific day. 15. FRI -- hours worked for per line of acct id on specific day. 16. Wk Hrs -- total hours for the week for per line of Acct Id. Question 1: what code do I need to automatically populate "Week Ending" field with the correct "dtmPayEndDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending). or Create a setup form in which the user selects "week Ending" date at beginning before entering Time Card data. Then from the setup form, data gets transferred/displayed into Main Form. If so, can someone assist in writing code that passes data from one form to another form or save setup form data into its own table. Question 2: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI)? No need to store total since it is only need to display in data entry form (subform) and printing report. Help!!! Sincerely, AccessRookie =) |
#7
|
|||
|
|||
Thanks for responding back to my messages. You have been a great help to
this project. Please see my answer inserted underneath your answers/suggestions. I'm very new to responding back to these messages. I figured that if I answered after your questions/suggestion then it will be easier for future members to follow along. Sincerely, AccessRookie =) "Steve Schapel" wrote: AccessRookie, The structure of your data (i.e. table design) should be dictated by the nature of the data itself. Allowing your table design to be influenced by what you want your forms to look like will often prove to be a mistake. Having separate fields for each day of the week is incorrect. Re-designing your form to meet the data requirements would be a better approach. Response to Steve: This project was something that sounded very easily to put together, but it turning out to become a nightmare with so many bells and whistles behind in designing its structure. Therefore, I have literally taken their manual Excel Timesheet and automated in turn, I can produce their current reports which they are currently manually calculating using Excel. I agree that table design shouldn't be dictated by this data entry form. Unfortunately, in this project, it is easier for me at this point to have a column for each day of the week associated with "dtmPayEndDate" column so that it will be easier for me to pull the information when they review data entered. As regards your question about the dtmPayEndDate entry, what is the relationship between dtmPayStartDate and dtmPayEndDate? Response to Steve: In tblPayrollSchedule table, explaination of data contents: 1. dtmPayStartDate is the day after dtmCheckDate 2. dtmPayEndDate is always a Friday's date 3. dtmCheckDate is pay check date How does the data get entered into dtmPayStartDate? I enter the data provided from Payroll department. Initially, I entered data for table from Excel, so for example: Row1: 1. pkeyPayrollScheduleId = 1 2. intPayYear = 2005 3. intPayPeriodId = 01A 4. dtmPayStartDate = 01/01/2005 5. dtmPayEndDate = 01/07/2005 6. dtmCheckDate = 01/14/2005 But when I initially entered this data, I was able to use formula on dtmPayStartDate, dtmPayEndDate for Row2 and beyond. Is dtmPayEndDate always 6 days after dtmPayStartDate? Response to Steve: Yes, for Row1, dtmPayStartDate is 6 days until dtmPayEndDate. But for next Row2, dtmStartDate on Row1 is 7 days until dtmStartDate on Row2. If so, you probably only need one of these fields. If not, please give an example of what you want to have happen. Response to Steve: I need to have all these dates in tblPayrollSchedule for future report pullings and don't want to do extra work to decipher by only using the dtmCheckDate. -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: No, that's not a good suggestion because, I have not posted the format of how the timesheet looks like, here is the detailed part: Acct Name Acct # SAT SUN MON TUE WED THU FRI Vaction 1111.1000 8 8 8 8 8 Each timecard detail has the specified labor allocated to each account number for the total hours of each day per labor expense. Therefore, it doesn't make sense in my situation to make it one field. I know I have not totally described how the timesheet look like in detailed since I can't attached any files to this message. But now, I have another issue, please see my revised table structure on tblPayrollSchedule and new questions: elp me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel. Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number 2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text 3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text 3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text 4. tblPayrollSchedule: 1) pkeyPayrollScheduleId = AutoNumber 2) intPayYear = Number 3) intPayPeriodId = Text 4) dtmPayStartDate = Date/Time 5) dtmPayEndDate = Date/Time 6) dtmCheckDate = Date/Time 5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayrollScheduleId = Number 6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number Here is the layout of Main Form in this order: 1. Pay Period Id -- combo box with primary key from tblPayrollSchedule and payroll date. 2. Week Ending -- automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 3. Employee Number -- combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number. 4. Employee Last Name -- automatically populates when Employee Number is selected. 5. Employee First Name -- automatically populates when Employee Number is selected. Here is the layout of Subform in this order: 1. Acct Id -- combo box containing a list of labor description that has associated fields as follows. 2. Acct Name -- Text Box will populate after selecting Acct Id. 3. Cost Center -- Text Box will populate after selecting Acct Id. 4. Acct -- Text Box will populate after selecting Acct Id. 5. Category -- Text Box will populate after selecting Acct Id. 6. Pay Type Id -- combo box that will populate Pay Type. 7. Pay Type -- automatically populates when Pay Type Id is selected. 8. Allocation -- Free form, to type comments. 9. SAT -- hours worked for per line of acct id on specific day. 10. SUN -- hours worked for per line of acct id on specific day. 11. MON -- hours worked for per line of acct id on specific day. 12. TUE -- hours worked for per line of acct id on specific day. 13. WED -- hours worked for per line of acct id on specific day. 14. THU -- hours worked for per line of acct id on specific day. 15. FRI -- hours worked for per line of acct id on specific day. 16. Wk Hrs -- total hours for the week for per line of Acct Id. Question 1: what code do I need to automatically populate "Week Ending" field with the correct "dtmPayEndDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending). or Create a setup form in which the user selects "week Ending" date at beginning before entering Time Card data. Then from the setup form, data gets transferred/displayed into Main Form. If so, can someone assist in writing code that passes data from one form to another form or save setup form data into its own table. Question 2: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI)? No need to store total since it is only need to display in data entry form (subform) and printing report. Help!!! Sincerely, AccessRookie =) |
#8
|
|||
|
|||
AccessRookie,
Thanks for your further explanation. I understand your comments about the project becoming more complicated than initially envisaged. And I understand the desire to do what seems to be easiest for the sake of short-term expediency... most of us have fallen into that trap at some stage! Many people, including myself, find a fairly steep learning curve with correctly using a database program like Access, but it is certainly worth the effort. -- Steve Schapel, Microsoft Access MVP AccessRookie wrote: Thanks for responding back to my messages. You have been a great help to this project. Please see my answer inserted underneath your answers/suggestions. I'm very new to responding back to these messages. I figured that if I answered after your questions/suggestion then it will be easier for future members to follow along. Sincerely, AccessRookie =) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
subforms with several subforms | Peter Höltschi | General Discussion | 1 | March 22nd, 2005 11:20 PM |
Disappearing Subforms | Bryan | Using Forms | 0 | March 3rd, 2005 02:17 PM |
Disappearing Subforms | Bryan | Using Forms | 0 | February 22nd, 2005 02:15 PM |
Disappearing subforms | Bryan | Using Forms | 0 | February 9th, 2005 04:27 PM |
Subforms crash using ODBC | Dick Kusleika | Using Forms | 0 | January 11th, 2005 08:35 PM |