Form-Subform-Button-RecordSelect

[TechKEYS]

I was not sure which category to post this question, so "access" it is.
This question is revolved around Design, Form, Subform and possibly some coding.

3 Tables - tbl_People, tbl_Pay, tbl_Schedule
tbl_People has ID fields that join the other tables



I created a form for tbl_People - with 2 buttons to open subforms
A subform for tbl_Pay & tbl_Schedule
All works fine when viewing a Person, I can click on my button for Pay and see that persons pay; same with schedule

Problem:
if a Person does not have a Pay Record or Schedule Record yet, my situation either produces an error or new record for the two related tables.
If tried some variations of coding to make it work without success.

Wish list:
I want to be able to see a person, IF that person does not have a pay scale yet, or a schedule yet, I want to be able to "Choose/Select" an existing record from those tables not create a new one, then the one that was selected now be part of the Person Table/PayID for next time the person is displayed in the form.

Working Example:
User will pull up "Bob"in a form; Bob has an associated Pay & Schedule Record, User Click said Button and display the relevant Date - This works as it should.

Non-Working Example:
User will pull up "Phil" in a form; Phil does not have a Pay &/or Schedule Record, I would like the User to be able to "Select" an existing Record from those tables where the ID's will now be part of that persons "People" Record for the next time someone pulls them up.

Design: I design the table structure this way because a "Person" pay scale can change frequently as schedule, so I want to keep the history of say Week 1 where Bob worked for $8 and 1st Shift; but Week2 he was a supervisor for $15 and happened to be on 3rd shift.

I would take design comments or just a fix to my Wish List listed above.
Much, much thanks

[June7]

Don't really understand the data structure. Is tbl_Pay to document actual pay for each employee each pay period?

If you want to provide db for analysis, follow instructions at bottom of my post.

[TechKEYS]

Sorry for the confusion - my apologies....

the Pay table holds several different pay scales; each scale can have up to 6 different amounts.
for example - scale1 might have rate1@ $10.00; rate3@ $22.00; rate4@ $17.00; $rate5@ 60.00

So, on the timesheet - the administrator will select "Bob" from the People; then enter Xhrs for item1; Xhrs for item3; Xhrs; for item4; and Xhrs for item5
The administrator will know where to enter the hours.

Its a bit weird; but these people get paid based on a few variables; What their position is at the time of the work (pay scale table); AND what shift they are currently on.
It can actually change during the day. Bob can submit a time sheet where he was on scale1 from 7A-12PM/1st Shift; then come back and submit a timesheet for 3A-5A on a different scale (Pay Scale Table) on the 3rd shift - based on what he is was doing and when he was doing it.

I don't have the DB in front of me at the moment; but it would look something like this
tbl_Person (PerID, Name, PayID, ScheID)
tbl_Pay (PayID, rate1, rate2)
tbl_Schedule (ScheID, Name, Start, End)
With the appropriate relationships

I thought about putting all in 1 table, but the persons pay and schedule can change at anytime even on the same day.
I want to be able to save the history ultimate on the timesheet - so if he worked 4hrs on monday at 1 pay scale & shift AND worked 2hrs at another pay & shift; I want to be able to see the history on the timesheet.

Not sure if this helps or I made this worse.
But much thanks in advance...

[ssanfu]

Just a FYI..... "Name", "Start" and "End" are reserved words in Access - shouldn't be used for object names. (See http://allenbrowne.com/AppIssueBadWord.html)

I don't have the DB in front of me at the moment; but it would look something like this

Structure (linking fields) look off to me.......

[June7]

Agree with Steve, something 'off'.

Time and Attendance db is one of the harder types to build, a big reason is that timesheet forms people like to track their time on don't conform to normalized data structure. Then there is the complication of distributing to multiple accounting codes and at different pay rates - as you have already noted. I used QuickBooks for accounting and payroll database.

tbl_Person should have only one record for each employee. This is master table of employee information.

Another table should have records for each pay period to document the employees being paid and the rates to apply. A record for each employee/date/code/rate combination.

At this point, those two tables is the simplest possible structure and entails lots of manual data entry into the 'payroll' table to document the timesheet. Some automation can be implemented to facilitate entering payroll records. The more 'user friendly', the more code.

[TechKEYS]

I've attached a DB that sets up the scenario that I'm trying to achieve.
This is NOT a payroll situation - I'm trying to specific cost for specific work at specific time.
a Payroll application such as quckbooks/peachtree will not work - its been tried

In the DB that I attached the "Jim Beam" person does not have an associated Pay Scale or Schedule
When I open the form to his record and click the "Pay Info" button - it opens to a new record (don't want that)
Would like to be able to "select" an existing record.

I might be over thinking it - but it is as the poster's suggested - I'm trying minimize as much as I can, the amount of manual entry so that the person filling in the timesheet will not have to select the person, then select the pay scale, then select the schedule - would ultimately like for the user to just pick the person.

Is this even possible.

I was not sure which category to post this question, so "access" it is.
This question is revolved around Design, Form, Subform and possibly some coding.

3 Tables - tbl_People, tbl_Pay, tbl_Schedule
tbl_People has ID fields that join the other tables

I created a form for tbl_People - with 2 buttons to open subforms
A subform for tbl_Pay & tbl_Schedule
All works fine when viewing a Person, I can click on my button for Pay and see that persons pay; same with schedule

Problem:
if a Person does not have a Pay Record or Schedule Record yet, my situation either produces an error or new record for the two related tables.
If tried some variations of coding to make it work without success.

Wish list:
I want to be able to see a person, IF that person does not have a pay scale yet, or a schedule yet, I want to be able to "Choose/Select" an existing record from those tables not create a new one, then the one that was selected now be part of the Person Table/PayID for next time the person is displayed in the form.

Working Example:
User will pull up "Bob"in a form; Bob has an associated Pay & Schedule Record, User Click said Button and display the relevant Date - This works as it should.

Non-Working Example:
User will pull up "Phil" in a form; Phil does not have a Pay &/or Schedule Record, I would like the User to be able to "Select" an existing Record from those tables where the ID's will now be part of that persons "People" Record for the next time someone pulls them up.

Design: I design the table structure this way because a "Person" pay scale can change frequently as schedule, so I want to keep the history of say Week 1 where Bob worked for $8 and 1st Shift; but Week2 he was a supervisor for $15 and happened to be on 3rd shift.

I would take design comments or just a fix to my Wish List listed above.
Much, much thanks

[June7]

My comments still apply. Still need a table for recording the actual pay data. This is the 'history' you want.

If you allow individual's pay to change hourly, then have to record the data that way - you said it can change in the same day.

PP	EmpID	PayID	DateWork	Hours
1	1	1	1/1/2015	4
1	1	3	1/1/2015	4
1	1	3	1/2/2015	8
1	2	4	1/1/2015	8
1	2	2	1/2/2015	8
2	1	1	1/5/2015	6
2	1	3	1/5/2015	2

The tbl_Pay is not a normalized structure. You record the PayID for the Scale into tbl_Worker but how do you know which rate to use?

Have you looked at MS Desktop Time Card database template. I don't know about 2013 but in 2010 it is listed under File > New > Database

[ssanfu]

Here is a "quick and dirty" modified example of your dB.

The structure is a little better - I say a little better because given this structure, you can not delete/change ANY records in tables tblPay, tblSched or tblWorker. The primary key fields are being stored in tblHistory. For example, in 6 months you change the pay for Scale 2 - $50.00 to $55.00, ALL of the historical records that had the PK for Scale - $50.00 will also change to $55.00.

Also, there is NO error handling.


You can/could add fields in tblHistory to store the actual values instead of the PK's. So when 2 hrs is selected, code will put 2 in the hours field and put the times into the correct fields. Then you can/could edit/delete records in the tables tblPay, tblSched or tblWorker.

Again, this is just an example!! Use at your own risk......