One data entry form or three?

[chriswrcg]

Should I try and create one data entry form or three? Associate information, Task information, Exception information?

[June7]

Might want to correct spelling of TaskExceptions.

There is no direct relationship between associates and tasks.

Normal data entry is 1 form for 1 table. Form/subform arrangement for data entry to master/dependent tables.

[ssanfu]

@chriswrcg.... maybe we could stick with 1 thread? No need to keep adding threads.
I modified the image of the relationships in the previous thread - I missed some field prefixes.



I would add the table prefixes. You need to develop a naming convention. There are many examples available - Google "naming convention".

The reason to add prefixes is to distinguish a table from a query. Consider "tblWorkDays" and "qryWorkDays". You could just name them both "WorkDays", but it will get confusing ...which is the table and which is the query. Also, the "jnct" in the table name "jnctTasksExceptions" means "junction" because the relationship between "tblTaskActivities" and "tblExceptionsList" is a many-to-many relationship. You need a third table to make a many-to-many relationship.
Another part of my naming convention is to add a prefix to field names - this helped to know which table the field in in.
I aldo add "_PK" and "_FK" suffixes to field name to be able to tell the linking fields in queries.


I use queries for form/report record sources, not tables.

Note that I changed most of the field names in the table "tblWorkDays". My bad...I changed table names and missed some prefixes.

Should I try and create one data entry form or three? Associate information, Task information, Exception information?

I haven't tried this yet, but I was thinking of a main form with two sub forms.
In the header would be the Associate information and an unbound combo box to select the Associate.
In the detail section would be the Work Day info.
In the footer would be two sub forms. The left form would be the Task information (sfTask) and the right form would be the Exception information (sfExceptions).

[GinaWhipp]

This set of tables is like haunting, they keep showing up. Hard to know where to comment. So, here are my observations\comments...

1. Clean up those Table and Field names (lower case letters are based on Table names, see https://www.access-diva.com/d1.html), it will lead to confusion as you get further into this.
2. I would do Main Form (Associates) and a Subform (Tasks). I would so a separate Form for Work Days but with a button to open from the Main\Form\Subform since that looks to be only needed a few times a day. I wouldn't want it to be in the way all the time.
3. No need for the _PK and _FK. Pretty obvious which is which and I hate underscores as it requires a 2 key press but that would be optional.

[chriswrcg]

This is what I am building right now. And of course there is a problem. This first image is design view.

When I go into form view however everything changes.

my detail or associate work day section is gone and my two sub forms switch to data sheet view? What have I done wrong this time?

[Rainlover]

Most likely data sheet view has been set in the properties box.

I think that your relationships are not 100%

Delete the relationship between tblAssociateList and the table tblWorkList. Note the slight change in the naming.

Create a relationship between tblAssociateList and the table tblTaskList.

I think that this is correct but you should wait to see if someone else comments.

Get rid of all of the prefixes in the field names. There is simply no need.

Further to what Gina said. Get Rid of the underscores and use Camel Hump.

The use of ID is redundant. Get rid of them as they do naught.


Hope this helps a little, or better still helps a Lot

[chriswrcg]

Create a relationship between tblAssociateList and the table tblTaskList.

I am getting an error message. Of course.


This is my relationship structure right now.

[GinaWhipp]

Well, a little confused because you started building before adjusting the Tables. The Detail section will not show and you have a problem trying to use a Continuous Form with Subforms.

Tasks Exceptions could a Subform on Daily Task.

I like *ID* to indicate which field is the Primary Key (PK) but old habits dies hard. The same for prefixes on the field name. When you write mostly code and fields could have the same name, ensures I know which table I'm pulling from.

[chriswrcg]

ok im getting a little frustrated. I went to form build because I thought my table and relationship structure was good. Apparently I was wrong. Lets address that first. How does my structure look right now? I am unsure what I should set as my primary key for each table? Should it always be auto number? I do not see the need for having associate list and task list directly linked. They are linked through the task activities table. This is the updated link structure. Is this good or do I need to change something up?

[GinaWhipp]

I don't understand the how AssociateList is associated to TaskActivities? Not going to work.

WorkDays only needs to be associated to AssociatesList no need to associate to TaskActivities.

[GinaWhipp]

Oops, posted a little too quick...

You probably do feel frustrated but relax, table design is the hardest part much like the foundation of a house. You get the foundation wrong and the house is going to collapse if it even manages to get built. Access is not as *easy* as Excel, relational databases are just not easy but we are here to help.

[GinaWhipp]

You are not trying to JOIN on Associate, it's AssociateID

[ssanfu]

First a comment. In a previous post, you placed an image of the spreadsheet.

Note the red arrow. It looks like you took the top row value, 324 (minutes??), and divided it by 60 (minutes??) to convert the 324 minutes to hours and minutes.
But what you have displayed is a TIME.
324 minutes is equal to 5.4 hours or 5 hours and 24 minutes.
You CANNOT write 5:23:52 as 5 hours and 24 minutes. (cannot write a time as a duration)

5:23:52 is a TIME... as in 5:23AM, NOT as duration (which is 5 hours and 24 minutes)

You cannot say "I took a trip that took me 5:23:52 (o'clock). (implied 5:23:52 AM)
You can say "I took a trip that took me 5 hours and 24 minutes (or 5.4 hours).

You need to understand the difference between DURATION and TIME.



OK, now onto the dB. I made a couple more changes in the table structures, mostly adding a couple of missing fields.
This is what I came up with - seems to work, but you didn't provide a lot of data to work with.


Good luck with your project....... I'll back out now.

[June7]

True, in a database for calculating elapsed time, need to use decimals. However, elapsed time such as race results are often presented in the format of HH:MM:SS. Simple Formatting cannot accomplish that. Requires code to convert decimal to the string. Same applies to latitude and longitude. Fairly common topic.

[Rainlover]

I always name the Primary Key as FieldPK. This should be related to another Table to the field FieldFK.

Others don't but I always use AutoNumber for the Primary Key.