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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

MwvarnerAuthor Commented:
That would help but how would I enter all 5 runs on the same form?
0
ButlerTechnologyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.