• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Access 2013 Joins

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
Mwvarner
Asked:
Mwvarner
3 Solutions
 
Dale FyeCommented:
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
 
MwvarnerAuthor Commented:
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
 
MwvarnerAuthor Commented:
The problem is going to be linking these tables so I can get all this data on the form at one time.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
clarkscottCommented:
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
 
hnasrCommented:
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
 
MwvarnerAuthor Commented:
Thanks Guys,

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now