Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2013 Joins

Posted on 2014-02-20
7
Medium Priority
?
396 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 49

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

877 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