Thread: Need Subforms?
View Single Post
  #7  
Old April 8th, 2005, 12:49 AM
AccessRookie
external usenet poster
 
Posts: n/a
Default

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