UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Employee Work Rotations, Access 2010    
 
   
DHillier
post Nov 9 2017, 06:53 PM
Post#1



Posts: 6
Joined: 9-November 17



Good evening,

I'm developing a database for my organization and I've managed to impress my boss with it's first implementation. Unfortunately, BECAUSE I've impressed my boss he has now presented me with a wish list of items for this database.

One of these things is a crew change calendar. We staff approximately forty vessels, and man of them have different crew change dates. Some ships crews use a 28 day cycle [e.g. 28 days on, then 28 days off] while others have a weekly crew change. To add a further complication the dates are staggered all over the calendar to give our HR department time to staff what they can. Oh and to top it all off, some ships work 28 and 28 during the winter and 6 weeks on and 6 weeks off in the summer.

At first I looked at the DateAdd command and then a query but I'm defeated everytime by the vastly different crew change dates. Is a table the best way to go about it or is there a better way for me to create this?

Many thanks in advance
D
Go to the top of the page
 
GroverParkGeorge
post Nov 9 2017, 08:50 PM
Post#2


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Based on your description, I would say that you should set up a table of work rotations by type and schedule. Then assign each ship to one of those work rotations for each time period.

--------------------
Go to the top of the page
 
DHillier
post Nov 10 2017, 08:00 PM
Post#3



Posts: 6
Joined: 9-November 17



Hi,

Thanks for the advice. Today I built a table using all the crew change data for all the vessels and then tried to tie it into the main forum using a lookup. The problem is that the lookup won't allow more than 20 fields at any one time.

Yes there's that many crew changes, up to 27 but there's one particular class of vessel changes every week on Monday. I didn't bother to put that one in.

I guess what I'm trying to do is something along the lines of placing the data in a table and then show: Date=[Vessel]CrewChangeDate>Today()

Thoughts?
D
Go to the top of the page
 
GroverParkGeorge
post Nov 11 2017, 07:22 AM
Post#4


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


"The problem is that the lookup won't allow more than 20 fields at any one time. "

That sounds like a serious design flaw in the table. Show us the table you created.

--------------------
Go to the top of the page
 
DHillier
post Nov 12 2017, 10:01 AM
Post#5



Posts: 6
Joined: 9-November 17



Database with table only attached. Pics attached as well.

Thanks again
D
Attached File(s)
Attached File  Database.zip ( 131.99K )Number of downloads: 14
Attached File  Sched_Pic_1.png ( 145.97K )Number of downloads: 12
Attached File  Sched_Pic_2.png ( 105.26K )Number of downloads: 7
 
Go to the top of the page
 
GroverParkGeorge
post Nov 12 2017, 11:36 AM
Post#6


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Yes, that's what we often call a "spreadsheet style table", and it's an unfortunate design choice. It's the kind of layout one is forced to use in Excel, but it is not appropriate for a relational database, such as Access.

Rather than try to implement a workaround with this lookup-which is nearly impossible anyway--let's step back, figure out how to create an appropriate relational table, and start again.

Go here and study the materials on Normalization -- or table design.

Then try again with a table where all of those columns are properly set up as records, or rows, instead.

--------------------
Go to the top of the page
 
DHillier
post Nov 13 2017, 04:17 PM
Post#7



Posts: 6
Joined: 9-November 17



I get the entities, and I think I understand the relationships [thanks Roger]. The problem is getting my data to work for me. I'm currently stuck on a query using a modified [read non excel like] table. You were correct about the lookup....that was pretty pointless. I guess I"m lacking in the code knowledge. Would this be worth trying in a Macro?

D
Go to the top of the page
 
doctor9
post Nov 13 2017, 05:09 PM
Post#8


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


D,

Before you talk about forms, queries, reports or code you must prioritize getting your table structure set up properly. That's the foundation for the house you're building. If the foundation isn't solid, the whole house is going to be difficult to work on because of all of the problems that keep cropping up.

Let's discuss the data you're working with. The two obvious data groups you have are Vessels and Employees Who Work On Vessels. Based on your screenshot, it looks like you are treating people as teams, or "Vessel Groups". I'm assuming these are teams of people that work together all the time, and move from ship to ship as a team, rather than as individuals where each ship crew is a mixed bag of people, different every time. Please correct me if I'm wrong on this. I'm worried that a "Vessel Group" might be a group of vessels, all of the same size/type. If so, you'd store the crew size in the table of Vessels.

Next, you have different types of Shift Cycles;
> Some ships crews use a 28 day cycle [e.g. 28 days on, then 28 days off] while others have a weekly crew change.

So, let's say for example, the ship Antioch has a 28 day cycle. It goes out on January 1 with a crew of 40 people. Then, on February 29, the Antioch comes into port and stays there for 28 days. On February 26, the Antioch heads out with a new crew of 40 people. Your example doesn't seem to need to track which individual people are on each crew, you just need a 40-person crew. Let me know if you need to populate each crew with individuals. I'm assuming there are different size crews for different ships in addition to different lengths of time they crew a ship.

Basically, you can identify a "crew" with the ship and the date that the ship leaves with that crew. If you don't need to track individuals, you can just log the date when a ship leaves with a new crew. Something like this:

tblCrewChanges
CrewChangeID [Autonumber, Primary Key]
lngShipID [Foreign key to tblShips.ShipID]
dteShipOutDate

If you DO need to track individuals, then you will need to track who is on which crew.

tblEmployees
EmployeeID [Autonumber, Primary Key]
strFirstName
strLastName

tblCrews
CrewID [Autonumber, Primary Key]
lngCrewChangeID [Foreign Key to tblCrewChanges.CrewChangeID]
lngPersonID [Foreign Key to tblEmployees.EmployeeID]

As you can see, you need to clearly define what data you're tracking before you design your tables. Do you need to track who was on which vessel on a certain date? Then you probably need a table of employees. That table will link to a table of Crew Changes, which then links to a table of Vessels/Ships.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
DHillier
post Nov 13 2017, 08:09 PM
Post#9



Posts: 6
Joined: 9-November 17



ok first of all I apologize for not going into detail earlier. I'll attempt to describe what I"m building.

I'm attempting to build a logging database for 41 vessels that my company uses. This is more to keep track of vessel activities as opposed to any individual employee. I used the term to 'Employee Work Rotations' to keep things simple. Originally the database was designed to record three three items, vessels operational messages in a canned format, vessel activities and upcoming vessel programs. Alot of the work was spent saving data entry time, and crew change was part of that aspect of the project. It used to be a manual entry via a spread sheet created by an excel savvy lady in my crewing section.

The basic interation is complete and the DB has been split and tested among the people that work in our operations center. My boss, who runs that shop, has asked if I could automate the crew change dates AND set up some sort of reminder to change the CO of the vessel on the crew change date. I am stuck on the table and am currently in my third version of it. We don't track any particular employee only the crew change date as it obviously affects the program the vessel is assigned to.

In referene to your 'tblCrewChanges', my table is called 'CrewChangeSked' and is in a relationship with 'VesselTombstone'. The tombstone table has the vessel name, a sailing order ID [unrelated to this] and a identifier for crew change called 'Crew Change Cycle'. This prefex representing the range of dates that cover crew change throughout the fiscal year, is what ties them together.

I tried to use a primary key but it was interfering with the tombstone table primary. And it turned out that the value indicated which crew change in the schedule.

I have never tried the foreign key as you have described yet. I will give it a go when I get back to ops tomorrow.

And yes it does help....quite a bit. Much obliged.

Hopefully I'll have more tomorrow.
D
Go to the top of the page
 
DHillier
post Nov 19 2017, 06:42 PM
Post#10



Posts: 6
Joined: 9-November 17



So I created the table as outlined by Dennis, and setting up the relationship between the VesselTombstone table and the CrewChangeSked Table I'm able to pull up all the crew change dates. Unfortunately it did little else. My next step was to try a query. This got me up to the current date but the remaining dates of the year also showed up.

The expression I used was >=Date() And that's where I am at the moment. Can I set up a second parameter in the query that limits the number of dates shown?

Later
D
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 02:17 AM