Solved

Access 2013 Too Many Fields

Posted on 2014-02-26
7
752 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 500 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 57
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

840 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