Query duplicates data

[Misiek]

Hello all,

Created a query based on one table, this table has few 1-many relationships.
Query works as it should be until I replace FK numbers with texts from related table. It works for all table but one. When I add table T_area query starts to duplicate records, but only in the query, doesn't make any changes to tables.

This is my relationship layout:

This is my SQL:

SELECT
 T_task.tTaskPK
, T_task.tTaskToDo
, T_task.tQAFK
, T_TaskType.ttName
, T_task.tTaskCompleted
, T_task.tTaskDueDate
, T_task.tTaskComplDate
, T_user.userName
, T_task.tUser2
, T_department.dDeptName
, T_area.aAreaPK
, T_task.tPriority
, T_task.tExecuteTime
, T_task.tMSP
, T_PartSourcing.psName
, T_task.tPMcard

FROM ((T_department 
INNER JOIN T_user ON T_department.dDeptPK = T_user.udeptFK) 
INNER JOIN (T_TaskType 
INNER JOIN (T_PartSourcing 
INNER JOIN T_task ON T_PartSourcing.psPK = T_task.tPartSourcing) ON T_TaskType.ttPK = T_task.tTaskTypeFK) ON T_user.uUserPK = T_task.tUserFK) 
INNER JOIN T_area ON T_department.dDeptPK = T_area.aDeptFK;

So far I tried: Grouping and changing INNER JOIN to any other possible but no luck.
Can you advice please.

Thank you.

 

[arnelgp]

replace your Inner Join with Left Join

 

[Misiek]

I tried that. Makes no difference at all, exactly the same result as with INNER JOIN :/

 

[HiTechCoach]

The issue is not the join type.

The issue is the design of your database with results in a many to many when you add the areas table.

The relationship between Department (1) to area (many) which means a Single department can have many areas.

The relationship between area( 1) to equipment (many) which means a Single department can have many areas.


In your original query when you add in the area table joined to to equipment table with field(s) from equipment table being return then you should get departments repeating for all of the equipment for all the areas linked to the depart. Is that what is happening? If yes then the query is working correctly based on the table design/schema.

Not sure what you are trying to do with this query.

If is is for a report then you might want to leave out the area and equipment tables for the main reports record source (query) and add them back in with a sub report.

 

[Misiek]

In your original query when you add in the area table joined to to equipment table with field(s) from equipment table being return then you should get departments repeating for all of the equipment for all the areas linked to the depart. Is that what is happening? If yes then the query is working correctly based on the table design/schema.

Oh I understand, Yes you are right.


No I don't need it for report (not just yet), it meant to be just to display all records for users, so they check it everyday and if task has been completed then they will tick a checkbox and/or add comments.

I've decided on current relationship design because each department has few areas and then each area has several equipments in it.
I thought this is the only correct way to get combo boxes in forms to display only related to each other areas and equipments?
I start to believe that I am wrong about this...
Can you please be so kind and instruct me on how I should change relationships?

 

[HiTechCoach]

I am not sure there really is a design issue. Without knowing more about what you want to model it is difficult to say if there is an issue with the design. Nothing jumps out at me as a normalization issue.

Your issue was probably due to the fact you had extra table that you did not need.

 

[Misiek]

Ok, the database is a tracker. We track incidents across few departments.
Each incidents has a location:
-department
-area
-equipment
Then also person reporting
-user (from the selected department)
Then each incident has an action hence table T_task
And that was it until few days ago.

And now we want to have an action tracker (task). We want to create actions for each department and area. But also want to see existing actions from incidents.

So I thought I will just add few fields to existing table T_task and add relationship to tables department and area.
But I got stucked with this query.

Does it explain the purpose?

 

[HiTechCoach]

Thanks for the information. That really helps.


Let me see if I understand what you you doing and what you need

You current design: You have incident that generate Tasks

You are now needing to create tasks that is not related to an incident.

Is that correct?

If yes, the simple thing would be to just create an incident for each department and area you want to track separately and and assign tasks.

You might add a field to the incident table to identify the type so you can filter report. This would also allow you to have separate forms for the different types if incidents. To the users they can appear to be different but in reality that are all the same tables.

TIP: If you spend the time to properly normalize your database design/schema then you can re purpose everything with very little changes if any.
-Boyd Trimmell aka HiTechCoach

 

[Misiek]

Yes thats right, you got it.

If yes, the simple thing would be to just create an incident for each department and area you want to track separately and and assign tasks.

I'm sorry, I don't understand. Do you mean create an incident as a table or as individual records as I am doing now?

You might add a field to the incident table to identify the type so you can filter report. This would also allow you to have separate forms for the different types if incidents. To the users they can appear to be different but in reality that are all the same tables.

Why different types? I don't understand the purpose, sorry, can you explain this to me please.

Please see below full structure

 

[HiTechCoach]

What I am suggesting is to use the already existing tables for your "action tracker".

I think that a incident is what the table t_qa represents. Is that correct?


From what I see in the table t_qa there may be some repeating fields which would lead to a normalization issue. Some field may be duplicating data which is another normalization issue. And there are fields that I would expect to be foreign keys but do not have the relationships defined.

 

[Misiek]

Yes that's right T_qa is my incidents.

I think I need some kind of relationship between t_qa and department and area and equipment as I need to know location of an incident. But then I also need T_task to have relationship with table department and area for my non related to T_qa tasks.

I'm just not sure if my current relationship layout if correct for this?

 

[Misiek]

Any more suggestions please?

 

[jdraw]

I think I need some kind of relationship between t_qa and department and area and equipment as I need to know location of an incident. But then I also need T_task to have relationship with table department and area for my non related to T_qa tasks.

I'm just not sure if my current relationship layout if correct for this?

Misiek,

Readers can not arbitrarily tell you whether your relationships (model) is a correct representation of your business or not. You really need to identify all of the business facts associated with your business.

Things along the lines of what you have:

1 Area can have 1 or Many Piece(s) of Equipment
1 Department may have 1 or Many Areas....

Sometimes things get clearer if you create a 3-4 line description of each of the Entities. It is through this process that determining what makes an X not a Y become evident. It is often a worthwhile exercise to "bounce" your definition/descriptions off others who may or may not have an understanding of your business. Those who understand can correct/agree/adjust the actual definition or suggest revisions. Those who do not understand your business can often highlight differences in terminology, too much jargon-ese, inconsistent terms/sentences...

You know your business and environment better than any reader. If there is a particular section, concept, relationship that seems confusing to you, tell us more about it and what seems to be puzzling.

 

[Misiek]

Table T_qa is the main one, it has field T_dept, where user selects department of incident. then based on this selection user will select a UserName and Area and Equipment.

1 department can have many areas.
1 department can have many users
1 area can have many equipments

1 record in T_qa can have many or none assigned records in T_task

-----------------------------

T_task
a task can be added without linking it to table T_QA.
User will have to select Department, Area and UserName (but no equipment)


------------------------------
Couple of post earlier I attached an image of my relationship.
I am not sure if this is correct, as in addition to current layout I used to have relationship between those tables:

T_qa - T_department
T_qa - T_Area
T_qa - T_equipment
T_QA - T_user

T_task - T_department
T_task - T_Area

After deleting the above 6 relationships I created a query based on T_task.
This query duplicates record if T_area is added. I dont understand why? Is it because my relationships are not correct?

 

[jdraw]

Some thoughts:

Table T_qa is the main one, it has field T_dept, where user selects department of incident.

So t_qa represents some Incident

then based on this selection user will select a UserName and Area and Equipment.

so every incident involves a User, an Area and a piece of Equipment

a task can be added without linking it to table T_QA.

suggests a task can exist (Incident free/no Incident)

Is T_QA much like an Accident Collection table??

 

[Misiek]

Yes to all of your questions.

Yes T_qa is incident collector

and
T_task is a task collector


Incident can have many or 1 or none tasks assigned to it.


Also need to be able to add a task for AREA not related to an incident.

 

[jdraw]

Incident can have many or 1 or none tasks assigned to it.

Perhaps, it is my being too far from the situation, but it seems

A Task may be involved in 0,1 or Many Incidents

Can you post a copy of your database? I'll look at the relationships.

 

[Misiek]

No.
Incidents can have tasks.

You created an incident there is a subform you can add a task.

When you create a standalone task, you have a separate form that's all you do just create a task and add department and area and user.

 

[jdraw]

What exactly is an Incident --plain English?
What relates a Task and an Incident?
Can you have an Incident independent of a Task?

Can you post a copy of your database? I'll look at the relationships.

 

[Misiek]

1.An incident - something unusual happened in an equipment and user has to report it.

2.User reports an incident, and can add a task where he/she will enter something like:
John has to inspect electric motor in area where incident happned.

3. I guess so, You report incident as a new record. You can add a task (as above) to this incident but you dont have to.

4. database is full of data, would have to strip it first.


I will try to explain purpose of this database on different example:

Imagine a storage warehouse. T_QA
Delivery van comes and you record what goods you unloaded and where you going to store them (T_department, T_Area, T_Equipment)
You add record to subform (T_Task)
1.John needs to unload the van.
2. Chris has to count items in each packaging.

Thats is it this is how T_QA works.

But you also want to add mini jobs for employees not related to any deliveries: (T_Task)
1.Michael go to storage location ABC and re-paint shelfs
2.John Go to storage location YXZ (T_department, T_area) and fix missing screw in shelving