need help to fix a circular table structure (sample database attached)

[justinhampton81]

I am working on setting up a table structure, which seems to logically need to have a circular relationship.

To explain:

Each structure may have several grades


Each grade may have several jobs
Each job may have several employees
Each structure will also have several employees...

I have set up the table structure as in the picture below, however, I'm already seeing problems running queries. I ran a simple query to say: show me all employees in structure 2, but none were shown. The query is only recognizing employees in structure 1.

Any suggestions for how to fix this? Is my SQL bad (I didn't attempt to manipulate it)? I've never tried a table layout like this, but I can't think of a different way, and this seems to make sense, though it doesn't appear to work.

Any help is greatly appreciated...

[June7]

I think problem is with Structure table. There are only two records, will there ever be more? If not, not sure I would even bother with this table. Regardless, appears to be an attribute of the Grade, don't have this field in tblEmployees.

The jointypes in the query are not correct, try:
SELECT tbl_employee.employee_name, tbl_employee.salary, tbl_employee.structureID
FROM (tbl_structure RIGHT JOIN (tbl_Grade RIGHT JOIN tbl_job ON tbl_Grade.GradeID = tbl_job.gradeID) ON tbl_structure.structureID = tbl_Grade.structureID) RIGHT JOIN tbl_employee ON tbl_job.jobID = tbl_employee.jobID;

Also, names should be split into separate fields for their parts - LastName, FirstName. Will save you a lot of headaches in the future. It is easier to concatenate fields than to split data.

[justinhampton81]

I think problem is with Structure table. There are only two records, will there ever be more? If not, not sure I would even bother with this table. Regardless, appears to be an attribute of the Grade, don't have this field in tblEmployees.

Thank you for your reply

Unfortunately, there will be several more. Each employee will actually be assigned to a structure. For example, a structure might be based on a location, so, employees in California might be assigned to a different grade than employees in New York. The structure table is designed to relate the appropriate grade to each employee. That being the case, I believe that it is truly an attribute of both Grade and the Employee...

Also, names should be split into separate field for their parts - LastName, FirstName. Will save you a lot of headaches in the future. It is easier to concatenate fields than to split data.

Thanks for the advice. This database is very much abridged just to demonstrate the problem at hand.

[justinhampton81]

The jointypes in the query are not correct, try:
SELECT tbl_employee.employee_name, tbl_employee.salary, tbl_employee.structureID
FROM (tbl_structure RIGHT JOIN (tbl_Grade RIGHT JOIN tbl_job ON tbl_Grade.GradeID = tbl_job.gradeID) ON tbl_structure.structureID = tbl_Grade.structureID) RIGHT JOIN tbl_employee ON tbl_job.jobID = tbl_employee.jobID;


Thank you, this solved the query problem, however, if I want to modify the query to include the appropriate minimum, midpoint, and maximum from the grade table, the result only includes the Primary Structure data points from the tbl_Structure.

see image below for query results, note that the min, mid, max are incorrect for the structure...



Thanks for helping troubleshoot this.

[June7]

Structure is an attribute of Grade or Employee, can't be both. The data is correct by the link through Job to Grade to Structure. Or maybe I have it backwards, employee link to structure to grade to job? Tried it, doesn't work.

Can grade be independent from job - as in government service, two different jobs (accountant, nurse) can have the same grade (GS-9). Job categories can be assigned a pay scale range. Nurse I is GS-5 - GS-9 and Nurse II GS-9 - GS-13, and the pay rate for an employee in each job category would fall within the range.

So I see you are entering the salary into employee record and this value is not by link to a pay rate table. You want a join as a check that the salary falls with the range?

I really don't know how the Structure code fits into this.

[justinhampton81]

Structure is an attribute of Grade or Employee, can't be both. The data is correct by the link through Job to Grade to Structure. Or maybe I have it backwards, employee link to structure to grade to job? Tried it, doesn't work.

Can grade be independent from job - as in government service, two different jobs (accountant, nurse) can have the same grade (GS-9). Job categories can be assigned a pay scale range. Nurse I is GS-5 - GS-9 and Nurse II GS-9 - GS-13, and the pay rate for an employee in each job category would fall within the range.

So I see you are entering the salary into employee record and this value is not by link to a pay rate table. You want a join as a check that the salary falls with the range?

I really don't know how the Structure code fits into this.

The structure would be driving the salary range associated with that grade - however, the employee (probably based on location and cost of living) drives the structure.

You are correct that the salary would need to fall within that GS range, however, it could be any number between the minimum and maximum of the range.

I've been playing with this in several ways, and I think that it truly is an attribute of both. Just can't figure out how to get it to work...

[June7]

Cannot be an attribute of both. Won't work. Either pick the grade record with the right combination of Structure and Grade values or Structure is independent of Grade and an attribute of Employee.

What do the Structure values (Primary, Secondary) mean?