What is this called?

[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

 

[mahenkj2]

Your post named "What is this called" seems to be involving two or three lines asking about a particular phenomenon but when opened it seems that you have published whole your database and asked so much (what?).

As one of experienced poster in this forum uses "In plain English what you want?".