Make a table from a table

[shazbot]

I am at the beginning stages of a project which ultimately will be a scheduling database for employees. I am looking for a way to automatically create tables from one table. I would like to create seperate new tables containing data and named for each column in the original table. For example the original table has a date column, and a series of other columns representing shift rotations named "m1", "m2", "d1", etc. Each record has a date and then the coresponding shift column contains the shift info(8a-4), (12a-8), (OFF), etc. We update our shift rotations once per year so I would need to run this procedure once to create the tables for each rotation rather than create the 90 some tables by hand. Thanks for the help. As you've probably already figured, I am relatively new using access so details will really help.

 

[MajP]

This sounds like a really bad idea and violates basic database design principles. Instead of asking how to do this, spend some time letting us help you get your table design correct. You do not want 90 tables, likely this can be done with a single table or a couple of tables. There are a lot of scheduling database examples out there. Provide as much detail on your table structure and desired capabilities.

You can describe your tables like

tableName
fieldOneName (what data type, any primary keys)
....
fieldNName (data type )

 

[shazbot]

Well, Ive got the following tables:
employee_table
employee_first_name (text)
employee_last_name (text)
employee_id (text)**pri key
schedule_rotation_id (text)

Pay_period_table
Pay_period_number (number)
work_date (date) ***pri key

schedule_rotation_table
work_date (date) *** pri key
M1 (text showing time period ex. 12-8a)
M2 (text)
......etc.,
The schedule_rotation_table is where I am having the problem. I cant figure out how the linking would work since the schedule_rotation_table doesnt have a schedule_rotation_id header. Since we have various rotations, 6 on 2 off, 5 on 3 off 3 on 5 off, etc. and the column limit is 255 (forcing me to use rows for date instead of columns since the table accommodates 365 days), I configured the table so there was a record for each day and each field/column contained the time period that
each schedule rotation (M1, M2, etc.) would work. On top of all that, some of the 12 hour rotations have an
8 hour day in the work week. I populated this table from an existing excel spreadsheet. Since Im currently weak
on the programming side (Im working on that) I dont know if there is a way to code a rotation and save starting
with a massive "pre populated" table. I've searched for some examples but havent been able to locate any.

 

[MajP]

I am not sure, because some of the rules I am not clear on. Here is my first suggestion, but may need to get refined with some more informtion

This table should only have information that uniquely identifies a person. Take any schedule keys out

employee_table
employee_id (text)**pri key
employee_first_name (text)
employee_last_name (text)


'Only info uniquely identifying the pay period. I would not use a date as the PK

Pay_period_table
pay_periodID (could be an autonumber)
Pay_period_number (number) Could this be a PK? Is this unique non repeating?
pay_period_start_date (date)
pay_period_End_date (date)


'Only info that uniquely identifies a shift
schedule_rotation_table
rotation_ID (could be autonumber. PK)
rotationName (text. If such a thing exists. Or something like 12Pm - 8AM)
startTime (dtm like 12:00 PM)
endTime (dtm like 8:00 AM
rotationDuration (in decimal hours. maybe not needed but can save some headaches later)

TblShiftAssignment
dtmShiftDate (date field for a shift assignment)
personID_fk (foreign key to person table)
rotationID_fk (foreign key to rotation table)

so values in the above table

1/1/2011 1 2
1/1/2011 2 2
1/2/2011 3 1

so the above table would say that on 1/1/2011 person 1 is assigned to rotation 2 and person 2 is also doing a shift type of 2. On 1/2/2011 person 3 is doing a rotation type of 1.

With the above table you can build a query that would bring your tables together and show all the related fields.
John Smith 1/1/2011 12-8a 12:00Pm 8:00Am

This normalized structure makes working with the tables and the data far easier. The hard part will then be making a nice user interface for both input and display. If this is an a "assignment" problem being able to see the results in a grid may be important. A crosstab query can often do this by putting row information into columnar information.

Also prepopulating the data, may be done in sql and not require any code. Can you describe the structure of the spreadsheet.

 

[shazbot]

Thank you for setting me straight here. The spreadsheet has columns laid out in the following manner:
Date M1 M2 M3
1-1-11 12-8a 12-8a 7-7a
1-2-11 OFF 12-8a 11-7a

Fairly straight-forward except for some of our 12 hour shifts where every pay period employees work an 8 hour day. Typically there are 2 employees working the same 12 hour rotation with one working thier 8 hour day one week, and the other the following week. This spreadsheet would be used at the beginning of the year once shift rotations, start times etc. are decided on. Since I currently lack the knowledge to use code to do this and it is familiar to those that decide on the various rotations, I thought a one time import of the spreadsheet would be easiest. Thanks again for the help.

 

[MajP]

Can you describe your spreadsheet a little more, I do not think I understood what you have and what capability you are looking for. Also what is your manual process for making assignments

If this is your spreadsheet, how do I read this? How do you see which person is assigned a shift? What are the columns? How many columns currently? Do the columns have meaning (m1 means.. d means...)?

following manner:
Date M1 M2 M3
1-1-11 12-8a 12-8a 7-7a
1-2-11 OFF 12-8a 11-7a

So for 1-1-11 there is an M1 of 12-8a and a M2 from 12-8a. Not sure what that shows.

I have a feeling you want 2 things. Away to update/display all required shifts and then an ability to assign people to the shifts.

Also kind you define what is a shift and what is a rotation in relation to your SS?

This may be a little bit of overkill, but it is to demo a general ID

http://www.4shared.com/document/fSz6Pfqr/RoomBooking_Ver_30.html

This is a scheduling database. This is probably more complicated because it is scheduling multiple rooms. But the general ID may hold. You have a table of people, table of avaialable shifts, and then a data table. The data table is normalized to hold each assignment. See if this is similar.

 

[shazbot]

Thanks for the reply, and I'll check the link out as soon as possible. Our shift times coupled with day off rotations are set up each year for the entire year and at that time employees are assigned to a shift rotation that way employees can plan days off and vacations accordingly. We have around 100 employees and 90 different shift combinations so I was stuck in the mind set of pre populating some of this (thinking in spreadsheet terms I guess). The spreadsheet above reflects all schedule rotations and the associated start time. In the above example, on 1-1-11, M1 and M2 would both be working at the same time, they would just have different days off during the pay period. The major sticking point for me is the rotating nature of some of the schedules we have. For example, our 12 hour shifts end up having a day on, start times/day off pattern that repeats every 14 days. If I can figure out a way to represent this I might be on my way.

 

[MajP]

1) So M1, M2, M3 represent a person? Reading down the column is that persons shifts for the year?

2) 12-8a, 7-7a,11-7a are shift identifiers. How many unique shifts are there? Can you list them?

3) You say there are 90 shift combinations. What do you mean by shift combinations? Can you provide an example?

4) If you are filling out this spreadsheet, with persons as column headers and days as row headers, how do you do the assignments now? How do you decide what goes in what cell? It sounds like there are some rules such as 12 hours shifts with a 14 day pattern