Solved

Access 2013 Joins

Posted on 2014-02-20
7
389 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
7 Comments
 
LVL 47

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

21 Experts available now in Live!

Get 1:1 Help Now