?
Solved

Access 2013 Too Many Fields

Posted on 2014-02-26
7
Medium Priority
?
806 Views
Last Modified: 2014-02-27
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
0
Comment
Question by:Mwvarner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39890425
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
 

Author Comment

by:Mwvarner
ID: 39890435
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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39890436
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Mwvarner
ID: 39890439
That would help but how would I enter all 5 runs on the same form?
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 2000 total points
ID: 39890462
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
 
LVL 58
ID: 39890534
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
 

Author Closing Comment

by:Mwvarner
ID: 39893110
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question