What am i trying to do?

[lightkeepr]

To start off with, I am a newbie to access but I am learning so much fast and I am in need of some direction of what to research to make what I need to happen.

Here is some background on what tables I have going:
[tbl_cg_shifts]
-ShiftID primary key
-School lookup value from [tbl_school_names]
-Address lookup value from [tbl_cw_locations]
-Shift_time text
-ampm lookup value from [tbl_cw_am_pm]
-active checkbox if shift is active
-shiftweight number for payroll hours
-GuardName lookup value from [tbl_guardinfo]
-Mon checkbox for shift works on monday
-Tue checkbox for shift works on tuesday
-Wed checkbox for shift works on wednesday
-Thrus checkbox for shift works on thursday
-Fri checkbox for shift works on friday

[qry_tbl_cg_shifts_only_active]
looks at the active checkbox and if it is checked all the values are passed and if not then all are blocked

[tbl_school_schedule]
-ID autonumber primary key
-School_date number for a date to be entered
-school lookup value from [tbl_school_names]
-Status lookup value from [tbl_school_status]
-reason textbox for notes



I have a form [frm_generate_payroll] that has a data entry box for a date and that box is named [txt_payroll_start_date]. Just below it is a command button named



[btn_generate_payroll]. When I select a date in the previous box and click the command button, I need the following to happen and I cannot figure out what is the best way to

achieve this:


So when [frm_generate_payroll] is opened and a date of 10/7/2012 is entered in the date box and then the command box is clicked, I want a few things to happen:
1. the date (10/7/2012) to be looked at in terms of what day of the week is it.
a. If it is a Sunday or Saturday, it can be ignored in the grand scheme of this as those days are not worked.
b. If it is a weekday (IE Monday 10/8/2012), examine each shift for the Mon checkbox from [qry_tbl_cg_shifts_only_active] and if it is checked, then that record is copied

into a table [tbl_payroll_shifts] and the following fields would to be copied: school, address, shift_time, shiftweight, guardname. I also want to add some new fields for this

table to be added in with each shift copied. I want the date being examined from earlier, a duplicate of the guardname field as guardname2 that defaults the data in the

existing guardname field and a final two fields as checkboxes for loga and logb.

So the new table [tbl_payroll_shifts] will have the following:
-ID autonumber primary key
-work_date date generated from [txt_payroll_start_date]
-school lookup value from [tbl_cd_shifts]
-address lookup value from [tbl_cd_shifts]
-shift_time lookup value from [tbl_cd_shifts]
-shiftweight lookup value from [tbl_cd_shifts]
-guardname lookup value from [tbl_cd_shifts]
-loga check box default unchecked
-guardname2 defaults to the same as guardname but can still be changed as a lookup value to change to another guardname
-logb check box default unchecked


c. Now I want the process to repeat for a total of 14 days (2 weeks) once for each day.

Now for a real kicker, what I would really like to have happen at the same time as everything is going on above, is have the school schedule table looked at and if a date and school match from [qry_tbl_cg_shifts_only_active] being processed, add a new field onto [tbl_payroll_shifts] and transfer the status field and reason field, and if reason was "out" to change shiftweight to a zero.

I am not looking for someone to make this happen for me, just information on what I am trying to do is called and i can try to figure it out myself and if I need more help I can come back with specific questions.

Thanks, Jeb Bruce

[June7]

Review recent thread https://www.accessforums.net/access/...nds-29020.html

Why do you want to duplicate data?

Add a new field programmatically? This indicates a poor db design. Access tables have a limit of 255 fields. If you keep adding them, will hit limit.

Payroll and time & attendance databases can be very difficult to build. You might be better off using off-the-shelf software.

Regardless, need to get data structure correct before proceeding with other development.

[lightkeepr]

Review recent thread https://www.accessforums.net/access/...nds-29020.html

Thankyou for the reference, I will review it and see what I can learn from it and use in my implementation.

Why do you want to duplicate data?

Because the initial data is for scheduling and assigning shifts and the second "duplicated" data is to track daily shift activity.

Add a new field programmatically? This indicates a poor db design. Access tables have a limit of 255 fields. If you keep adding them, will hit limit.

I am trying to add only a few fields that will be used for each record. I might have not clarified that in the OP.

Payroll and time & attendance databases can be very difficult to build. You might be better off using off-the-shelf software.

If there was an off the shelf product I would have already gone that route for this very unique situation. Believe me that I have tried many options and they do not do what I need them to do.

Regardless, need to get data structure correct before proceeding with other development.

Care to clarify what you mean when you say the data structure needs correcting first?

[June7]

I am just saying make sure the data structure is correct before committing to development and implementation. I thought you had requirement to programmatically add new fields as well as duplicating data between tables which indicated structure was not optimized. On review, maybe by 'add some new fields' you did not mean adding fields to table structure and you actually meant just 'populate data into two additional fields'. That makes sense.

Seems to me could save shiftID instead of duplicating all data.

Your post covers a lot of ground and I expect all you want can be done but can't offer specifics. Good luck and when you post thread that focuses on specific issue, we will try to help.

[lightkeepr]

Lets take this in small chunks then and maybe I can get somewhere.

Lets start with this:
I have a form that has a textbox called txt_payroll_start_date and a user can enter a date (10/8/2012) as the start of a 2 week pay period that attendance will be tracked for. When a date is entered, a button is then clicked and to start with, I need Access to be able to tell what day of the week it is. If it is a Sat or Sun, they can be skipped or ignored. If it is a mon through Fri, analyze the table called tbl_cg_shifts and copy every record that the checkbox for that day of the week is active. Example: the date entered initially is 10/14/2012 and the button is pressed to start the process. Because the initial date was a sun, it is ignored and then the date is advanced by one date to 10/15/2012. Because this would be a monday, each record with the checkbox field called mon, that is checked, from tbl_cg_shift (fields: ShiftID, shiftweight, guardname) would be copied to tbl_attendance and the date of 10/14/2012 added to the date_worked field in the tbl_attendance.

I would want this process to keep adding one date to it for 14 days at a time to complete the pay period.

Does this make sense and a little more broken down?

Thanks in advance, Jeb Bruce

[June7]

That will involve quite a bit of programming. Too much to deal within in one thread.

Take one step at a time.

For a start, did you review the thread I referenced that dealt with calculating day of week? Get that working for you then move on to programmatically creating records.

[lightkeepr]

I have a value called processday and when i do an append query, I want to pass that value (a date) into a field called date_worked on table tbl_payroll_shifts for every record processed. Any suggestions on what I should research on how to do this?


Here is the SQL code for the append query:


INSERT INTO tbl_payroll_shifts ( School, address, shift_time, shift_weight, default_guard, working_guard )
SELECT tbl_cg_shifts.School, tbl_cg_shifts.address, tbl_cg_shifts.shift_time, tbl_cg_shifts.shiftweight, tbl_cg_shifts.GuardName, tbl_cg_shifts.GuardName
FROM tbl_cg_shifts
WHERE (((tbl_cg_shifts.Active)=Yes) AND ((tbl_cg_shifts.Mon)=Yes))
ORDER BY tbl_cg_shifts.ampm;

How would I modify this to run the same thing from the vba code?

[June7]

CurrentDb.Execute "INSERT INTO tbl_payroll_shifts (School, address, shift_time, shift_weight, default_guard, working_guard) " & _
"SELECT tbl_cg_shifts.School, tbl_cg_shifts.address, tbl_cg_shifts.shift_time, tbl_cg_shifts.shiftweight, tbl_cg_shifts.GuardName, tbl_cg_shifts.GuardName " & _
"FROM tbl_cg_shifts " & _
"WHERE (((tbl_cg_shifts.Active)=Yes) AND ((tbl_cg_shifts.Mon)=Yes)) " & _
"ORDER BY tbl_cg_shifts.ampm;"

[lightkeepr]

any suggestion on how to add on each record that is inserted the processday value into the field date_worked?

[June7]

Where will processday value come from - user input to a textbox or default of the current date?

CurrentDb.Execute "INSERT INTO tbl_payroll_shifts (School, address, shift_time, shift_weight, default_guard, working_guard, date_worked) " & _
"SELECT School, address, shift_time, shiftweight, GuardName, GuardName, #" & Me.processday & "# AS DateWorked " & _
"FROM tbl_cg_shifts " & _
"WHERE Active=Yes AND Mon=Yes " & _
"ORDER BY ampm;"

[lightkeepr]

June7-
Why couldn't I find it that simple in all the searching I did before I asked again here? That is so much simpler than the examples I found searching! Thanks for your help! I have a new conundrum now and I have been kicking this one around for a short while and cannot get anything to work right. I am still looking for vba code to do the following:

value: txt_payroll_start_date
Value: payroll_end_date


tbl_payroll_shifts
-date_worked
-School
-Address
-Shift_time
-shift_weight
-default_guard
-working_guard
-school_status
-school_reason


tbl_school_schedule
-School_date
-School
-Status
-Reason


After everything is created for the time span using the previous code you helped me with before, I want to have access then examine the tbl_payroll_shifts table and tbl_school_schedule table. If during txt_payroll_start_date and payroll_end_date I have a record in tbl_school_schedule that has the following info in a record:
School_date 10/15/2012
School Draper
Status OUT
Reason School Holiday

How can I have access look at the records (only between txt_payroll_start_date and payroll_end_date in the date_worked field) in the tbl_payroll_shifts and look for the same date (date_worked and School_date), and school name (School and School) and when a status is OUT, update the school_status to equal Status from tbl_school_schedule and update the school_reason with Reason and change shift_weight from a 1 to a 0 (zero)?

[June7]

Sounds like you need an UPDATE query. I just don't understand description of your data structure enough to offer specifics. An UPDATE query can have filter criteria to limit the records that will be updated. Again, code can execute the sql:

CurrentDb.Execute "UPDATE ..."

Review http://office.microsoft.com/en-us/ac...010076527.aspx

[lightkeepr]

June7-
Why couldn't I find it that simple in all the searching I did before I asked again here? That is so much simpler than the examples I found searching! Thanks for your help! I have a new conundrum now and I have been kicking this one around for a short while and cannot get anything to work right. I am still looking for vba code to do the following:

value: txt_payroll_start_date
Value: payroll_end_date


tbl_payroll_shifts
-date_worked
-School
-Address
-Shift_time
-shift_weight
-default_guard
-working_guard
-school_status
-school_reason


tbl_school_schedule
-School_date
-School
-Status
-Reason


After everything is created for the time span using the previous code you helped me with before, I want to have access then examine the tbl_payroll_shifts table and tbl_school_schedule table. If during txt_payroll_start_date and payroll_end_date I have a record in tbl_school_schedule that has the following info in a record:
School_date 10/15/2012
School Draper
Status OUT
Reason School Holiday

How can I have access look at the records (only between txt_payroll_start_date and payroll_end_date in the date_worked field) in the tbl_payroll_shifts and look for the same date (date_worked and School_date), and school name (School and School) and when a status is OUT, update the school_status to equal Status from tbl_school_schedule and update the school_reason with Reason and change shift_weight from a 1 to a 0 (zero)?

I have tried the link you gave and it is what I want to do, but I cannot get the update query to work right, I keep getting errors and when i search the help file for the error I get even more confused.
Let me try re-wording what I am trying to do and see if if may help.

Limit the update query to look at records on [tbl_payroll_shifts] using the two values of [txt_payroll_start_date] and [payroll_end_date].
if [tbl_payroll_shifts].[date_worked] = [tbl_school_schedule].[School_date] AND [tbl.payroll_shifts].[School] = [tbl_school_schedule].[School]
then copy the value in [tbl_school_schedule].[Status] to [tbl_payroll_shifts].[school_status] and copy [tbl_school_schedule].[Reason] to [tbl_payroll_shifts].[school_reason]

Does this make sense any better?
Thanks for all the great help and great links to help teach me more!

Jeb

[June7]

I would have to see the sql statement to analyze. Or provide db for analysis. Follow instructions at bottom of my post.

The sql should be a join of tbl_payroll_shifts and tbl_school_schedule. The UpdateTo row under [school_reason] would have [Reason].

[ssanfu]

Jeb, from your first post:

[tbl_cg_shifts]
-ShiftID primary key
-School lookup value from [tbl_school_names]
-Address lookup value from [tbl_cw_locations]
-Shift_time text
-ampm lookup value from [tbl_cw_am_pm]
-active checkbox if shift is active
-shiftweight number for payroll hours
-GuardName lookup value from [tbl_guardinfo]
-Mon checkbox for shift works on monday
-Tue checkbox for shift works on tuesday
-Wed checkbox for shift works on wednesday
-Thrus checkbox for shift works on thursday
-Fri checkbox for shift works on friday

[tbl_school_schedule]
-ID autonumber primary key
-School_date number for a date to be entered
-school lookup value from [tbl_school_names]
-Status lookup value from [tbl_school_status]
-reason textbox for notes

It sounds like the fields in red are look up fields, defined in the table.
Look up fields are generally considered BAD. This might be why you are having problems with your update query.

If they are look up fields, you should read these two pages:
http://access.mvps.org/access/tencommandments.htm
http://access.mvps.org/access/lookupfields.htm