Solved

Access 2013 Too Many Fields

Posted on 2014-02-26
7
724 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now