Dont understand relationships

[johnherrerajuan]

Hello
I’m fairly new to access and I do not understand relationships
I want a table that has a Primary key as an SRNumber which is like a workorder, this table will include Labor, Materials
The second table I want it to be a Labor table, which has a combo box with a list of labors, an hour field, hourly rate field, overtime, and a total

The third table I want a material table with a combo box(which has a list of materials), a Quantity field, and a description field
In the end I want to create a form where I have an SR number at the top and all these three tables combined.

I hope I’m making sense

 

[TheRooseIsLoose]

So I understand - in your form, you want 3 input boxes to appear? Users would type in an SRNumber, and select from a dropdown of labor, and another dropdown of materials, possibly viewing the properties of each labor and material after they've picked it.

If that's the case, you'll want to keep your overall table structure (because it's fine), and then you would define one-many relationships between:
1. Labors table, labour column (one) and Primary table, Labour column (many)
2. Materials table, Material column (one) and Primary table, Material column (many)

Then, you can specify the details about each Labor/Material in the respective reference table.

Strictly speaking, you may not actually need to define the relationship using access' buit in relationship diagram thinger. You can just build tables that have working relationships. The purpose of the relationship thinger is to avoid inconsistencies and mistakes (so making sure every time someone picks a material, it's a valid material, for example). But, it's good practice, and it would make viewing aggregate data in the Primary table much simpler (click the plus icon to see the related data about each SR).

 

[Mihail]

I don't think that yours tables setup is good.
More, I think that you don't fully understand the business that should be managed with your database.

For example the same Labor can has many hour rates.
Is not the same if you punch steel, aluminium or gold.
Also, is not the same if you use an worker with one year of experience or another one with 30 years (at least in my country)

Again, the time for the same labor is very different:
That depend from the material, from the machine, and, mostly, from the parts that should be machined. You can do the same "Labor" to machine a huge part in 3 days or to machine small parts in 5 minutes. The necessary time for change the parts should be considered and has nothing to do with the type of labor.

Any way... I've sketched a starting point for your DB (see attachment) but, because is a complicate task, I think that is a lot beyond of your skills at this time.

Good luck !

 

[gemma-the-husky]

relationships

given a set of tables, a primary key in one table, will be represented by a foreign key in another table. this is a one-to-many relationship

eg 1 department has many employees. in order to model this, the employee record stores the department id.

so each employee is in only 1 department, and a single department may have many employees.

now if this isn't a correct representation - eg - an employee can be in more than one department - then this structure isn't correct.

in the latter case you need 3 tables

employees
memberofdept
department

the memberofdept table is a junction table between the employee and department table.

1 employee can be in several memberof records (1-to-many), and 1 depot can be in several memberof records (1-to-many)

----
data always needs to be analysed into 1-to-many relations. A many-to-many relation has to, and can always be divided into 2 1-to-many relations.

----

now given the relations, access can also impose relational integrity. that is the one side of a 1-to-many relation HAS TO exist.

so given a 1-department-to-many-employees relation, with relational integrity set - you get an error if you try to enter an employee into a department that does not exist - which protects against mis-spellling, and other inadvertent errors.


---
hope that helps