One data entry form or three?

[chriswrcg]

The relationship between the task exceptions table and the exception list table is going to have to stay as it is. I have shuffled, deleted, re-created, renames, changed field types, and everything else I could think of to swap the one-to-many relationship so the many is on task exceptions rather than the exception list; and I can not make it work.. The last error message I got was a freaking paragraph. I officially hate access.

[GinaWhipp]

Hmm, well is it okay to fix this the way I would have it?

Also, I just need to understand something. Work Days are just the days people worked and Task Activities are the Tasks they did during that day, correct?

[chriswrcg]

My first thought when creating this was to combine associate data together and then have task and exceptions as separate tables. It was later suggested I separate the associate data into two tables. One for personal information like name, shift, and department. and then have workdays be for their day to day work information like date, clock in and out times, lunch duration and so forth. Yes, you are correct about the task activities and the task. Just a record of all the jobs they do during a day.

I know I am close to getting this right but it was suggested that I swap the relationships for my task list and exception list so that the many (infinity symbol) is on my main tables with the 1 being on the lookup tables. I was able to do that with task list but just cant make it happen with exception list.

You can show my how you would do it if you would like.

[GinaWhipp]

Okay, I have redone.

1. You do not seed to combine First Name and Last name in a Table, that is what queries are for, see qryAssociateInfo
2. Since Work Days is a one a day entry doesn't belong between Associate Info and Task Activities and will work better when filtering on what Tasks were done on what day.
3. List Tables are for combo boxes on Forms no need to store redundant data within the Table they are linked to. You can see that information in a query.

Note, I also moved data around so when viewing in queries you can see what you need to see.

Remembering the Forms you had this scenario should work out quite nicely. You can look up Tasks and Work Days easily. The Main Form would be for Associates and one Subform for Work Days and another for Task Activities. You can add a pop-up for adding Exceptions OR a Subform (but pop-up might be better).

[ssanfu]

OK, one more try.........

tblAssociateInfo - there is ONE record for each Associate in this table.
The field "aiShift" SHOULD NOT be in this table unless the Associate will only ever work one shift. (not my design)
Field "aiShift" should be in table "tblWorkDays" as ""wdShift"

tblWorkDays - this table holds the days worked by an Associate.

tblTaskActivities - this table has the tasks (max 15 per OP) that an Associate worked on a specific day.

Using this structure

how would you know which tasks were performed on which day?


The OP has never posted the requested workbook, but here is a partial image


It appears this is tasks per day per Associate.




Post #20 has an image of what I consider the proper design.


Post #13 has the basics of a working dB.



Good luck with your project......

[GinaWhipp]

@ssanfu (Steve)

Correct not you design it was what I suggested. I downloaded the sample from a previous post. That said...

1. I agree that if an Associate works more than one Shift it should not be in that table, however, that is not the norm. Will wait to hear back from OP.
2. I also see I forgot to add Date to Task Activities. So, the OP has a choice switch back to Task Activities between as you had it or add a Date field with you can default to *today's date*, so no need to type.

[GinaWhipp]

Hmm, the more I think about it why couldn't Work Days be put in the same Table as Task Activities with a Task Type as Work Day and then you could separate with a query? You would still get the totals you want. The name of the field in the Table is irrelevant because the Form would show as Clock In and Clock Out, as well as, Lunch which could be a separate field or a Task Type.

Just thinking out loud, inviting feedback...

[chriswrcg]

This is the DB right now.

Marked in red circles is the task exceptions table and the exception list table and the relationship they have right now. The red line is the one I want to create using exception code as the one that unifies and making sure the many is linked to the task exceptions table. @SSANFU (Steve) mentioned in post #25 that

table relationships for tblTaskActivities/tblTaskList and tblTaskExceptions/tblExceptionList are backwards!

So I have been trying to get that done with no success on the exceptions side.

Now, talking about the Associate info and work days tables it will be necessary to go into more detail.
I agree that we can put the shift information on the workdays table. Most associates only work one shift unless they are coming in for some overtime. If an associate starts out on first shift but happens to work longer and into the second shift they do not have to change shifts from 1 to 2. On a daily basis you work the shift you start on all day regardless of the time on the floor. The only time they would put down another shift than the one they are assigned is when they are coming in on their day off and working another shift. Right now we run three shifts 7 days a week. weekend shift is Sat-Monday. First and second shift is Tues-Friday.

On the Associate info table the fields that will never ever change is name (last,first), and Department (home department). sub-department (inbound, outbound,) might change as productivity and what is heavy-light changes. and as mentioned above shift.

Work day is primarily Time info. Clock in and out time, The date you are working, how long you took for lunch, (Shift could go here)

Task Activities is what you did today. Task Code (what job are you doing) Task Number (Job 1, 2, 3 and so on), task start and stop time, How many trips you took to do the job ( warhouse work is done in a circle. wither you are loading/unloading a truck, put-away product, or picking product. Most of it is dock-bin-dock), and Standard ( How long each circle should take you on average.)

Exceptions are what stops you from performing a circle. wither you are taking lunch, changing a lift battery, having a meeting with your boss, or dealing with a problem you find you have stopped production ( making the circles) and are doing something else. The computer does not care. From clock in to clock out the computer (or system) expects you to be making movements (doing your job). In a 10 hour work day with the standard being 10 minutes a circle, the computer expects 6 bins an hour or 60 bins in a day if you only did 40 then why? Exceptions allow you to "stop the clock" while you are dealing with the issues.


All that being said the normal flow for an associate during the day is this.
This is who I am, this is the day I am working and the total time I am working, these are all the jobs I was able to complete today, these are all the problems I had to deal with during my day.

[GinaWhipp]

Why wouldn't the Exception List table be tied to the Exception Code? Is that what the Exception Code is?

[ssanfu]

On the Associate info table the fields that will never ever change is name (last,first), and Department (home department).

Sounds good. Now the problem is how do you/would you distinguish between two Associates named "Jim Smith"? You need an employee number ... or something.
I once worked at a place where there were two employees named "James (Jim) Darling" and three named "James (Jim) Smith".

sub-department (inbound, outbound,) might change as productivity and what is heavy-light changes

Might need another table or move the field "sub-department" to the table tblWorkDays". You would need to determine how important keeping track of the sub-department is to you. Not enough data has been provided for me to make a suggestion.



In response to your PM, no, you do not need to have "tblTaskList" or "tblExceptionList" tables. But having them reduces the chance of entering an unknown code (task or exception). BTW, in the dB you posted, "kkms6.accdb", there are two exception codes with the same code but different descriptions:

eExceptionCode	eExceptionDescription
NP	North Pod
NP	Waiting on North Pod

In setting up relationships, you have to remember which is the primary key (PK) field and which is the foreign key (FK) field for the 1-to-many relationship.
For the tables "tblTaksList" and "tblTaskActivities", the relationship is correct.
For the tables "tblExceptionsList" and "tblTaskExceptions", the table tblExceptionsList is the one side. It should have one unique record tied to many non-unique records in "tblTaskExceptions".

This is how the relation should be set up: