Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2013 Joins

Posted on 2014-02-20
7
Medium Priority
?
395 Views
Last Modified: 2014-02-24
I have started a new access desktop database project for tracking production in our printing department.   I have a main table with general production data.  I have an hour table that will keep track of all employee hours and rates.  I also have a supplies table that will track supply cost.

I had originally tried putting all fields in one table but there are too many fields to track to do this.

I am have a great deal of difficulty linking both the hours and Supplies table to the main table at the same time.

I hope point me in the correct direction.
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 48

Expert Comment

by:Dale Fye
ID: 39875057
It would be helpful to know a little more about what production you are trying to track (do you have more than one product?) and whether you are trying to link that production back to a specific employee.

Generally, I would expect that you would track employees in one table, employee rates in another (because their hourly rates will change over time), and then hours worked in another.

How you would relate that to your production data, would depend on what "production data" you are trying to track.

I would generally think that whatever it is you are producing would require more than one item from your supplies table, so you would probably have another table that would identify which product (without more info on your products this is difficult) to the type and quantity of supplies used for that product.

It wouldn't surprise  me if there should be some other tables in there as well.
0
 

Author Comment

by:Mwvarner
ID: 39875072
I am tracking production in a printing operation.  I have a basic job table with the JobID and the number of pages total copies, Etc.

I have a employees table that has name, position, regular and overtime rates.

I have a supplies table with the name of the supply, quantity used, cost each and total cost.
0
 

Author Comment

by:Mwvarner
ID: 39875079
The problem is going to be linking these tables so I can get all this data on the form at one time.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 39876437
Do you track time by the JOB, or by the EMPLOYEE?

In other words, do you care that Joe worked 2 hours on Job 101, and then 3 hours on Job 189? Or do you just care that Joe worked 8.5 hours today?

Do you buy SUPPLIES for a specific JOB, or do you pull job items from STOCK?

Capturing manufacturing data can be very complex, so as fyed suggests you need to provide us with much more detail of your operation before we could suggest a good structure.

Note that trying to "get all this data on the form at one time" is generally not the best way to represent data. It's fine to combine data for a Report, but in general a Form is used to enter specific bits of data. For example, you'd probably have a form that includes information for Jobs, another for Employees, another for Supplies, etc. You might "issue" supplies to Jobs, and you might "assign" an Employee to a Job, but you would not need to know everything about an Employee to assign them to a Job - you'd just select the Employee to assign to the Job, and leave it at that.

It's not uncommon for novice developers (or their bosses) to want a "dashboard" type form, but those rarely work. In almost every case, the data will be not updateable, and the overall design and usability of the form is severely impacted.
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 668 total points
ID: 39876757
You could use one table for the form's MAIN data, then create a subform for the other table.  You could update data from both "parent" and "child"(subform) tables.

Scott C
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 664 total points
ID: 39877475
Check this article about inner joining tables. It may help you moving forward faster with your project.

http://www.experts-exchange.com/Database/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
0
 

Author Closing Comment

by:Mwvarner
ID: 39883498
Thanks Guys,

All these inputs were very valuable and I've been able to workout my issues and I'm moving on.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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