One data entry form or three?

[chriswrcg]

Again I'm sorry I didn't see this. I had marked the string as solved and didn't come back to it to see if anyone else had posted to it. I really like the DB. IT is a great foundation and leaps and bounds better than what I am trying to hack together.

On the frmMain data input form I can't see how to add work date, shift, clock in and out time on the header. I see it in design view but it is not showing up in form view.

Something I don't understand. I notice that you have query's set up based on the tables. you have tblworddays and qryworddays. I thought queries were just for calculations which is what I have been trying to use if for. Why do you have query's based on tables? Sorry, just trying to learn.

Thank you again for all your work.

[Rainlover]

Can you post a cut down version of what you have. Include some tables so we can have something to test.

Anything that has nothing to do with the current problems should be deleted.

Test before sending and wind it back to the oldest version you have. 2003 or 2007 would be good.

----------------------------------------------------------------------------------------------

Instead of writing a SQL statement the query grid does this much easier and faster.

It is great for new people. I use them because they give me a good visual.

SQL can be difficult.

[chriswrcg]

In my excel version of this I do all my calculation in minutes as it is easier and more straight forward. When I show the results to the associates however not everyone can look at 324 minutes and know that that is over 5 hours. I added the time conversion at the bottom just for clarity and for the benefit of the associates to know just how much time they were really dealing with.
The full time cell is never used in any calculations.

[chriswrcg]

I stripped it all back down and am making sure one step is completed before i move to the next. This is it.

[ssanfu]

I"m guessing that Posts #16 & 18 were meant for me.



Post #16

On the frmMain data input form I can't see how to add work date, shift, clock in and out time on the header.

They are not in the header, they are in the detail section (light green background). They cannot be in the header because each associate can work multiple days.
BTW, I looked at the dB kkms6.accdb posted in Post #19. The relationships are set up wrong (IMHO)

Consider this:

1) This is the table that has all of the associates info.
2) Each associate can work 0 or more days. This table stores the days worked. This includes work date, shift, clock in and out time.
3) Each work day can have 0 or more tasks
4) Each Task can have 0 or more exceptions.


Looking at the relationship view in "kkms6.accdb", you have the tasks table linked to the associates table. How do you/would you know what tasks were performed on which day? You do not have tasks linked to the work days table. Same for the exceptions table - it is not tied to the task table.


-----------------------------------

Something I don't understand. I notice that you have query's set up based on the tables. you have tblworddays and qryworddays. I thought queries were just for calculations which is what I have been trying to use if for. Why do you have query's based on tables?

99% of the time I use queries as the form record source. With a table as the form record source, ALL records will be displayed (unless you set a form filter). With a query, it is easier to limit and sort the records. And you can have multiple queries based on the same table but can have or display records in a different sort order on different forms. For me.
I feel I have better control using queries are form record sources.


---------------------------------------------
Post #18

In my excel version of this I do all my calculation in minutes as it is easier and more straight forward

It would be nice/helpful to see the spreadsheet, instead of a partial picture.....

[chriswrcg]

I need to understand relationships better. I was just linking like fields. If a table has associateID, I link it to another table with associateID. And I assumed I had to put associateID in every table or almost to build relationships. The more I learn the more I figure out how little I understand.

[ssanfu]

Which is why I use the suffix "_PK" and "_FK" (although you don't need the underscore). You only need to add a FK (foreign key field) IF the tables need to be related.

Have you worked through the tutorials at Rogers Access Library? Don't just read - actually do the lessons.



DESIGN info:

About Normalization: Here is a link to a 5 part article on Normalization
What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization?



The Normal Forms (5 parts)
=========================
The Normal Forms: Introduction



"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

[chriswrcg]

I think I got it. What does everyone think?

I added an Associate list lookup table for quickly adding and subtracting associates as they come and go through the company.
I also removed the door and picker data from the DB. I used it in my spreadsheet but found that there is such a thing as to much data based on the end result you are going for. I do not need them overall for the queries I will be utilizing and the reports I will be putting out.

[GinaWhipp]

My only comment would be to combine tblAssociateList and tblAssociateInfo but other than that it's great!

[ssanfu]

I agree that tblAssociateList and tblAssociateInfo should be combined.

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

Think about it. For each work day there are going to be task activities. The table tblTaskList is EVERY (all) task. This is the list you select which task activity you perform.
The tblExceptionList table holds EVERY (all) exceptions.



In the above image, please explain how you will have a task activity will have an entry from tblTaskList. If you enter a task activity in tblTaskList, you will only be able to have one task ID - ever. Remember, tblTaskList is a list of ALL tasks available - the tasks are unique - you should NEVER have multiple records for a task "LW"!

In the above image, please explain how you will have one or more exceptions for a task entered from tblExceptionList. If you enter an exception in tblExceptionList, you will only be able to have one exception ID - ever. Remember, tblExceptionListis a list of ALL exceptions available - the Exception are unique - you should NEVER have multiple records for an exception "MM".

Try manually adding data into the tables to see if it is possible.






This is how I designed the tables/relationships for the tasks and exceptions.

See the difference?

[chriswrcg]

I can't get the tbltaskexceptions and tblexceptionlist to link up in a many to one relationship.


This is how the tables are set up.


how do I change a one-to-Many into a Many-to-one?

[GinaWhipp]

The Exceptions List is just a look-up table right? Then it should link to eExceptionCode.

[chriswrcg]

That's what I tried but I got back "no unique index found for the referenced field of the primary table" error code. What the heck does that mean?

[GinaWhipp]

What is means is you tried to link a two fields and neither of them was a PK. So, let's just fix this... can you upload a sample file please?

[chriswrcg]

Post #23 has a ziped file and post #26 has images of what I am seeing. For tblTaskException the PK is eExceptionID witch is an auto number. For tblExceptionList eExceptionlistID is the PK and is also an Auto Number. I can make eExceptionCode in the tblExceptionList table the PK but it will have to be a text field.