Access 2013 Too Many Fields

I've been tasked with building a new database to track our print operation.  It must track multiple customers with multiple products each.  For each edition I need to track up to 5 press runs  production values.  I also need to track supplies used and by far the largest part is tracking regular and overtime hours for each associate in each part of the operation.  That means hours, rates, names, total regular and overtime cost.   I started with one main table with everything in it but that didn't last long.  I split my data into multiple tables and that helped.  I need to be able to enter all this data on one form.  I can't seem to figure out how I'm going to do that.  I created a join query but there are still too many fields for one query. I'm attaching a copy of what I have so far and hopefully someone will be able to point me in the right direction.  The names and pay rates in this database are all made up at this point, no real people or pay rates.
Print.accdb
MwvarnerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ButlerTechnologyConnect With a Mentor Commented:
I recommend using a list box on the main form that shows all of the runs associated with the production.  You can then create a separate form that allows you to edit a run.  The list box action of double click will open the run form focused on the specific run.  You should also have a new and delete button that will create a new run for a the particular production and a delete.

Hope this helps teaching class -- so I can't reply with follow up for a few hours.

Tom
0
 
Kelvin SparksCommented:
I don't use A 2013, so can't play with your db. It sounds if you need to look at sub forms and sub reports, rather than joining all data back up to a flat record.

This will reduce the number of fields, as you shouldn't be joining parent records to child ones.

Kelvin
0
 
MwvarnerAuthor Commented:
The last thing I tried was using a subform but I can't seem to get it to display anything but a data sheet view.  I would like for it to contain the same information displayed like the tabs control I'm using now.   If you know how to make the subform display differently that would be a great help.  I can't find any place to change the default view.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ButlerTechnologyCommented:
I would recommend adding another table to handle the R 1-5 from the production table and have a 1 to Many relationship.  This will allow a production to have many Runs and allow you to reduce the number of columns.

Tom
0
 
MwvarnerAuthor Commented:
That would help but how would I enter all 5 runs on the same form?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You need to setup your DB so that there is one table for each "thing"; Customers, products, production runs, Employes, Hours Worked, etc.  Then the fields that pertain to only that thing go in that table.  

This is called normalization and there three basic rules you need to follow:

1. Every field should be "atomic" - that is comprised of a single value.  Something like "2 Hammers" is not a single value.  What you have is a value that is a qty, and a value that describes something.  They belong in separate fields.

2. All the columns pertain to the "thing" the table describes, which is what I mentioned above.

3. All fields must be mutually independent.  For example, you have price, qty, and extended price.   Extended price is qty * price.  It is not independent.  It relies on price and cost for its value.

  One of the other ones is in the definition of what a table is; that is every row needs to be uniquely identifiable.  If our table is about customers, then I need to be able to tell one customer from another.

 If I had a table like this:

Customer Name
ABC Company
XYC Company
ABC Company

 by name alone I may or may not have two of the same companies.  But if I add additional fields:

Customer Name    City           state   phone
ABC Company       Buffalo      NY    716 999-2394
XYC Company        Cleveland OH   216-459-9999
ABC Company       Dallas        TX    393-000-2020

  Now it's obvious I have two different instances of a customer.

  That in a nutshell is how you design a relational database (bit simplified of course, but that's general idea of it).

  So first, look at your table and determine what "things" that data is about.  Then group the fields into tables for each one of those.  Then ask yourself if by looking at any row you can tell it apart from the others.  If not, you need to add more fields.

  I can't look at your DB right now, but will a little later.

Jim.
0
 
MwvarnerAuthor Commented:
Thanks Tom,
I sort of went the way you described.  Instead of doing that on the run1 through run 5 I decided to do it to the hours.  I already had an hour table created so I did as you said and it works very well.  I may still have to go back and do it to the runs as well.  Really depends on how many more fields my operations folks decide they want to track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.