Table Template

[perryc]

All,
I have a table with a template schedule for an orientation of new hires. The tables contain fields like Date, Event, NewEmpID...What is the best way to duplicate the template and update dates and NewEmpID?

(template)
Date: 01/03/2022 Event: Meet with John NewEmpID: 1
Date: 01/04/2022 Event: Meet with Mary NewEmpID: 1
Date: 01/05/2022 Event: Training1 NewEmpID: 1

I want to duplicate this template and change the date and NewEmpID for all the events...
Date: 04/11/2022 Event: Meet with John NewEmpID: 2
Date: 04/12/2022 Event: Meet with Mary NewEmpID: 2


Date: 04/13/2022 Event: Training1 NewEmpID: 2

I know I can manually change the dates and NewEmpID, but, is table has 50rows, may take a while and can make mistakes.

Ty in advance.

Sincerely,
Perry

[CJ_London]

You need a primary key which could be negative to indicate It is a template. Or you can have a separate’istemplate’ field

[Micron]

Looks to me like this is about multiple tables for the same things, the difference being who know what - perhaps month or year. That would be the wrong approach. Might also be that meetings should be in one table and training in another lest it become difficult to separate the 2 things.

[ArviLaanemets]

From your explanation, it's quite vague what you are trying to do, but I'm trying to estimate what this must be!

You have to keep some schedule table, and there are some recurring events you need to apply to this schedule table on regular basis.

You must have the schedule table, like:
tblSchedule: EventID, EventDate, EventType, EventDescription, ... (EventID is an autonumeric integer field, EventType has values indicating Meeting, Trainng, etc.)

You must have the employees table, like:
tblEmployees: EmployeeID, EmploymentDate, Forename, Lastname, ... (EmployeeID is an autonumeric integer, in case you need to enter unique identifier manually too, add an additional field and create an unique index on it)

To automate new events, you need a table where you can define any regular events - like:
tblRegularEvents: RegularEventID, DayNo, MonthNo, Period, EventType, ..., LastRunDate (RegularEventID is an autonumeric integer. Period has values indicating the event is added daily, weekly, monthly, or yearly. DayNo and MonthNo are filled or left empty depending on Period)

And as last step, you create a procedure, which:
1. Checks tblRegularEvents for events which were scheduled to be added to tblShedule after LastRunDate;
2. Checks tblShedule, to be sure any events returned in step1 weren't added earlier;
3. For events of types handling new employees, gets list of new employees with EmploymentDate > LastRunDate
4. For every EventType from step1, runs another procedure,which adds events of according type from list of events gotten from step1 and step2 to tblEvents
5. As last step, overwrite LastRunDate with current date (not datetime!)

You can run this procedure manually (from button on form), or you can run it from OnOpen event of your main form.

[perryc]

Thank you all so much for your insight. I will think about what you have suggested. I will likely ask more questions later.

[perryc]

Ajak, That's a good idea.

[perryc]

Mircon, I hear you. That's true. You are much more organized than me. I will give it some thought.

[perryc]

Arvilaanemets, ty for your detail thoughts. Let me try to explain a little more of what I am trying to accomplish. So, in excel, there is these formula that when one value is changed, the subsequent value can be changed. e.g.
Cell1 = Date1, Cell2 = Date1+1, Cell3 = Cell2+1... only 1 date is changed (Cell1) and subsequent cells are updated automatically. Can this be done in Access? I have no idea because it is a table now. How do I define Tablevalues with a formula?

[ArviLaanemets]

How do I define Tablevalues with a formula?

Don't do this with formula in table! Instead do it using queries or procedures, which are run by form/control events

Generally, a table is something you work with when you create it. After that, less you (or any user) dabble with it directly, the better!

[perryc]

Well, the problem is, the values (dates, events...) need to be repeated from the template whenever the next new hires start and we will repeat the whole thing, except the dates will change. I do not know how to approach this best besides manually copy and paste the entire schedule template and then change the dates accordingly. Ty anyway.

[CJ_London]

is there a principle that the second event follows a set number of days or weeks after the first and the third after the second, etc? if so, subsequent dates can be determined from the initial date. Your initial example shows 1 day difference, but that may be because it is not a real world example.

Also it may be that the 'description' should just be 'meet with' or 'training' and you then specify with who or what type of training.

[perryc]

The date are mostly weekdays (M-F). The first day is known. The subsequent dates are be populated but skip the weekends (no one will train on the weekends). However, I need to be able to update these dates or descriptions at anytime.

[CJ_London]

easy enough to skip weekends - what about bank holidays as well?

[perryc]

Well, we only observe 6 major holidays. Memorial, Jul 4, Labor Day, Thanksgiving, Christmas and NY day

[CJ_London]

so you will need a table to store those dates for each year (I assume if Christmas is a saturday or sunday, the bank holiday is the monday). If the selected day is a bank holiday, skip another day.