Solved

Access 2013 Joins

Posted on 2014-02-20
7
393 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 (Access MVP)
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 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 167 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 30

Assisted Solution

by:hnasr
hnasr earned 166 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
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 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