OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   Using Forms (http://www.officefrustration.com/forumdisplay.php?f=24)
-   -   Need Subforms? (http://www.officefrustration.com/showthread.php?t=215455)

AccessRookie March 26th, 2005 12:15 AM

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 =)

Steve Schapel March 26th, 2005 09:01 AM

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 =)


AccessRookie April 6th, 2005 11:57 PM

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 =)



Steve Schapel April 7th, 2005 12:08 AM

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 =)


AccessRookie April 7th, 2005 08:25 PM

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 =)



Steve Schapel April 7th, 2005 10:16 PM

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 =)


AccessRookie April 8th, 2005 12:49 AM

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 =)



Steve Schapel April 8th, 2005 09:30 AM

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 =)



All times are GMT +1. The time now is 02:56 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
OfficeFrustration.com