Query duplicates data

For my purposes I don't need the data. I'm interested in the structure.

1. So an Incident involves an Equipment, but does not necessarily involve a Task. I'm just trying to get the details --so a machine/equipment is used independent of a Task?

2. If a user reports an Incident why and why not would he report/associate a Task?

These sound like steps within some Task
1.John needs to unload the van.
2. Chris has to count items in each packaging.


"mini Jobs" -- is that work/task that doesn't fit somewhere else?

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)

Are Tasks created every time you are about to do the work? It doesn't seem like you have repeatable jobs/tasks.
Eg:
-Remove and replace wheels
-Unload truck and place materials in Area 5

Are you primarily in the moving/receiving business?
 
Last edited:
1. So an Incident involves an Equipment, but does not necessarily involve a Task. I'm just trying to get the details --so a machine/equipment is used independent of a Task?
yes thats right.


2. If a user reports an Incident why and why not would he report/associate a Task?
I have made a mistake, yes user would always associate a task to an incident.
ex:
Incident: Toxic liquid spill on the floor
Task: Clean up & ensure floor surface not damaged.

Are Tasks created every time you are about to do the work?
Yes. Yes t might seems like repeated, but they are not, so user need to enter task description manually.
"mini Jobs" -- is that work/task that doesn't fit somewhere else?
basically yes. An independent of any records in table T_qa (incidents)



No nothing to do with moving/receiving, it was just a made up example of giving someone a task to do, which isn't related to whatever happened in other part of business, so forget it :)
____________________________
Please see attached DB.

Open DB with Shift.
Query Q_task doesn't work as it should, it repeats record if T_Area added.

Then Form F_taskTracker is based on this query.
 

Attachments

Hi, any luck on this one please?
 
Had difficulty opening database. Once opened, not sure what I'm looking at. Hopefully someone else can understand what you have and need.
 
When you open it up. On right hand side there is a TRACKER button.
A form will open in data sheet view. The query it's based on gives me too many records. The query duplicates records.
I can't work out why. This is where I need someone's help please.
 
When I open your database it takes about 30 seconds to show a screen. There is no apparent means to look at design view. I did click on tracker and there seem to be many replicates of records. But I don't know what it is suppose to show, and you have not given me any instruction as to how to look at the code behind form controls and events.

Suggest you post a version that gives me opportunity to look at design view.
 
As I mention in previous post:

____________________________
Please see attached DB.

Open DB with Shift.
Query Q_task doesn't work as it should, it repeats record if T_Area added.

the database has menus and options disabled unless you open it holding SHIFT key.

Then look at design of query: Q_task,
there is 480 records, but when I add table _Area to the query it multiplies all records and gives over 3000 records.

I dont know where is problem and how to fix it, but as it was previously mentioned, there is potential problem with relationships.

Can you look at this please.
 
Misiek,
I opened the db with shift key down. I takes 30-45 seconds doing something when I enable Content. I have attached the resulting screen. How exactly do I get to design view?
 

Attachments

  • Misiek.jpg
    Misiek.jpg
    94.4 KB · Views: 104
I hold the shift key down then double click it and hold it until you see all tables, queries and forms on left pane.
When you open first time you had to enable content, then you need to close the DB and open again with shift please.

When you open it and as you say it takes longer, its because it calculates data for all those cells on the main from.
 
Ok I closed and opened a second time. I rearranged your join graphic to get rid of overlapping lines. see attached.

Now, what is it I'm looking at in business terms? What do you think this query is doing -- in plain English?
Here is the reformatted SQL
Code:
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.aAreaName
	,T_task.tPriority
	,T_task.tExecuteTime
	,T_task.tMSP
	,T_PartSourcing.psName
	,T_task.tPMcard
FROM T_TaskType
INNER JOIN (
	(
		T_PartSourcing INNER JOIN (
			T_department INNER JOIN (
				T_user INNER JOIN T_task ON T_user.uUserPK = T_task.tUserFK
				) ON T_department.dDeptPK = T_user.udeptFK
			) ON T_PartSourcing.psPK = T_task.tPartSourcing
		) LEFT JOIN T_area ON T_department.dDeptPK = T_area.aDeptFK
	) ON T_TaskType.ttPK = T_task.tTaskTypeFK;
 

Attachments

  • qtask_misiek.jpg
    qtask_misiek.jpg
    37 KB · Views: 91
the query shows all records from table T_task.
In fields where there was a number as foreign key, it is replaced by actual text from assigned tables so user can see useful data not just numbers.
 
Ok, let's get to the core. Why do you think t_area doubles the record count?

How does area relate to Task (business terms, plain English)?
How is area related to equipment? What is the purpose of all the subdatasheets? I turn that off in a database.

You said there are really about 400+ tasks. When you add t_area you got to 3860??

When I look at t_defect, I see id 6 -13 all relate to defectclas "Co". What does that mean?

I don't know your environment and have little to no idea of your business, and can't start to guess at what this all might represent.
And you have not described the business in simple English.

How come there is no t_Incident in your database? I thought this was all about Incidents?
 
1. Thats the problem I am trying to resolve - I dont know why it doubles record count...

2. Task basically a job to do in specific location - in Area
Each area has several machines in it, i call them all Equipment
Dont know about subdatasheets... :/

3. same as point 1. I dont know why it does it.

4. Table defect is only related to table T_qa. Each incident will belong to an item on the list from table defect, its defect classification. this part works ok so dont worry about that :)
I cut all data in tables to 2 characters to there is still data but unreadable to anyone from outside of the business for data protection reasons.

Table T_qa is incidents. in the new query Q_tasks, I want to see all tasks those assigned to incidents an d those that aren't. thats why T_qa doesnt take part in this query.

That was my original relationship layout before i extended table T_task

5zdk.jpg
 
I'm sorry, but I do not know enough of the business facts to make any real review. You said early on it could be your relationships, and that is quite possible. Usually tables and relationships are built from the business facts/rules and are intended to support the business. You can test your model(tables and relationships) with some test data and some scenarios. If your model is a true representation of your business and your test data and cases are realistic, you should get the expected results when you test the scenarios against the model.
It seems to me that you are unsure of the model and do not have clear description of the business. The model seems to have been put together, rather than design based on the business. So the model and the database design could very well be the root of your issue.
It also seems you have done a lot of development work based on the number of tables, queries, forms and reports. If these are built on a flawed database design, it might be difficult to trace the source of your observed error.
You have seen enough threads and posts to recognize a theme that says get your tables and relationships designed to meet your business before getting too deep into Access..
Perhaps someone else reading this thread understands what you are trying to do and can offer help and some steps to correct whatever is causing your excess number of records.
Good luck with your project.

Because you know your environment and the numbers you should be getting, why not
start with a small query (related to your task query) and start adding tables incrementally in an attempt to isolate the "culprit table or join"? Get a query to work, then add a little more complexity. If you take this approach, and check the results each time; then add another table and recheck your counts etc. you should be able to identify something.
 
Misiek,,

I had stopped monitoring this thread since I thought we solve the original question.

I will take a look at it and read through the post since my last reply to get up to speed.

I I see you have posted a sample of your database. That helps a lot.. I will take a look.
 
HiTechCoach,

Hi, have you had any chance to look into this database please?
 
HiTechCoach,

Hi, have you had any chance to look into this database please?

I have been messing with it some as a user. You have left all the custom user UI stuff enabled which makes it difficult to do anything as a developer. I was able to disabled the Ribbon and the autoexec. I am now able to look around.

I will let you kmow what I figure out soon.
 
Last edited:
Hi,
Any luck on this, please?

Note sure what you did to the ribbon. What you posted has some much stuff disable it was very difficult to look at anything.

I finally end up importing all the objects into a new blank database so I could look around. I have attached it is any one wants to look at this.

After looking around I still think my original suggestion should work.

I fixed your tracker form. I get 481 records. The isue was as I originally posted. You have tables that should not be n the query causing the duplicates.

See the attached with the Tracker for updated.


Part 2:

I would add a field to the qa table to define the record type. I would create a qa record that is type "department tasks" Most of the fields will not be needed for this record type. You may even need to add a few fields.
 

Attachments

Users who are viewing this thread

Back
Top Bottom