Query between two tables

[JCabral]

Micron




The problem is solved, thanks for your advice, unfortunately when we run into a problem that didn't start with you, that has implications with other files that are not your responsibility and the only thing you have to do is try to solve a problem that you were asked, that's it that happens.
Fortunately there are forums like this and people like you who help us solve these problems, thank you all so much!

[June7]

JCabral, everything in #27 is for your consideration. The UNION then CROSSTAB is an alternate way to manipulate the data.

Copy/paste the SQL statements and explore as you see fit.

[JCabral]

june7


I'm sorry, but I can't execute those instructions you refer to, there will probably be intermediate steps that I'll have to do, and that my level of knowledge doesn't allow, I'll study to see if I understand what you're referring to.


In the meantime I'll see if I can apply your query to my tables, and see if the results are as expected.


Thank you very much for your help

[JCabral]

For now I have a request to make to everyone who helped me. It is widely believed that the tables are poorly made.


Taking into account the problem that I put here, the data at stake and that I put here, how should the tables be made so that what I asked for was easy to solve?


What I requested: For a given OIA and for a given Route, what is the total length of inspection carried out on each day of the week.

[June7]

If you normalize table structure, then the CROSSTAB I showed would be possible to generate the aggregation you want. The UNION query shows what a normalized table could look like. Would need to add a table with unique route records.

NROUTE	ROUTE
1	R-VRL1
2	R-VRL2
3	F-Férias_1
4	F-Férias_2
5	R-VCT1

Then table for the daily data. This is a 'junction' table to associate routes and tasks in a many-to-many relationship.

ID	NROUTE	Task	Dy
1	1	TAREFA #0001	MON
2	1	TAREFA #0002	MON
3	1	TAREFA #0003	MON
4	1	TAREFA #0004	MON
5	1	TAREFA #0005	MON
6	1	TAREFA #0006	MON
7	1	TAREFA #0007	MON
8	1	TAREFA #0008	MON
9	1	TAREFA #0009	MON

Again, need to fix the primary/foreign keys (TASK field should hold IDTASK, not task designation) as well as the inconsistency of OIA data between the two tables. OIA should not be in both (needs to be resolved even if don't normalize).

I have already shown that what you have can be handled. Whether or not the effort required to change structure is worthwhile is for you to decide.

It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

[JCabral]

june7


As I suspected, your query, which solves what I had requested from the beginning!, only works in case the "TASK DESIGNATION" have a unique designation in tblTasks.
As I mentioned later in my actual table this does not happen.
Is it possible for you to redo the query for this case? Or is it too complicated?
If it's too complicated let me know.


Regarding the normalization of the structure of the tables, is it possible to make the BD_Tasks file available with the normalized tables so that I can see if I can implement this in the real tables?


Thanks again

[June7]

Show me an example of data reflecting the described situation.

Attached is normalized structure for the posted database. It does not fix the OIA issue.

Many would use a number for day instead of name because names sort alphabetically not by day order. Can either have a lookup table for days or calculate conversion between day name and number. I did not change your field so you decide how to handle.

Attachment 50558

[Micron]

You might also want to research db normalization to see if you can grasp the path you need to take.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[JCabral]

June7

These are the tables with the data as the original, I just changed some sensitive data.
In principle the query should give the same results but no, I think it will be because the "RoadDesignation" are not unique, ie they are repeated, Is it? Is there a solution for this case?

[June7]

Okay, I see what you mean by duplication. The OIA field makes record unique. This is a reason should be saving ID into tblRoutes instead of RoadDesignation value if you want appropriate OIA value associated with each day. Cannot get correct output by directly joining tables with current structure, period.

tblTasks

IDRoad	OIA	RFR	UO	RoadDesignation	District	kmINI	kmEND	WeeklyFrequency	ToInclude	Comments	InspectionExtension	WeeklyObligation	LABEL
996	dsousa	rmfcosta	COGP	TASK #0434	District #05	3.77	12.74	1.00	Yes		8.97	8.97	1
1162	fiferreira	rmfcosta	COGP	TASK #0434	District #05	3.77	12.74	1.00	Yes		8.97	8.97	1
1351	hlourenco	rmfcosta	COGP	TASK #0434	District #05	3.77	12.74	1.00	Yes		8.97	8.97	1
1740	vmmmartins	rmfcosta	COGP	TASK #0434	District #05	3.77	12.74	1.00	Yes		8.97	8.97	1

If duplication follows this pattern - where InspectionExtension value is same for a RoadDesignation set - can use a query that returns DISTINCT RoadDesignation and InspectionExtension records from tblTasks for joining to tblRoutes 5 times. Unfortunately, I found 4 RoadDesignation's that have different InspectionExtension values.

tblTasks

IDRoad	RoadDesignation	InspectionExtension
2777	TASK #1072	29.28
2492	TASK #1072	29.27
2778	TASK #1073	16.03
2493	TASK #1073	16.04
2781	TASK #1076	0.61
2496	TASK #1076	0.81
2783	TASK #1078	34.40
2498	TASK #1078	33.40

[JCabral]

Hi June7


I'll see what's going on, it could be a rounding problem, but one thing is for sure, the InspectionExtension is always the same for a given RoadDesignation, what you detected is an error, which I'll check.

Thanks

PS: Fixed

[June7]

Then you need a table where there is a unique record for each RoadDesignation and not duplicate data to multiple records.

What about the other fields that show duplication - all same for a RoadDesignation?

Need to determine where OIA should be saved. Exactly what does this value represent?

I just noticed when I pasted records into post, it did round to 2 decimals, however, numbers are still different. I also just realized this is a calculated field in table. kmINI and kmEND are showing variations affecting the calc.

[JCabral]

OIA is name of the person who does the inspection.

The reason there is duplication is because one OIA normally does its own road inspections, but when another OIA is on vacation it does the other OIA's road inspections. Therefore, I need to know which are all the roads that may be assigned to a certain OIA in order to be able to make "normal" ROUTES and holiday ROUTES

For a given "RoadDesignation" the only thing that can be different is the OIA, all other values are the same, if this is not the case it is because there is an error, the same as the one you detected, that is two different OIAS can have the same road, when I talk about the road I'm talking about "TASK #xxxx"

[June7]

Then you also need another table related to Roads table to show this relationship. This also leads to the need for a table of people - tblEmployees, save EmpID instead name. Probably similar requirement for RFR field.

There are also variances in District and WeeklyFrequency for a RoadDesignation set.

IDRoad	RoadDesignation	District	WeeklyFrequency
2646	TASK #0985	District #11	1.00
2405	TASK #0985	District #12	1.00
2412	TASK #0992	District #11	1.00
2653	TASK #0992	District #12	1.00
2468	TASK #1048	District #11	3.00
2753	TASK #1048	District #11	2.00
2469	TASK #1049	District #11	3.00
2754	TASK #1049	District #11	1.00
2481	TASK #1061	District #13	1.00
2766	TASK #1061	District #13	2.00
2948	TASK #1333	District #14	1.00
3388	TASK #1333	District #14	2.00
3032	TASK #1417	District #13	1.00
3175	TASK #1417	District #13	2.00
3472	TASK #1417	District #13	2.00
3188	TASK #1430	District #13	1.00
3485	TASK #1430	District #13	2.00
3045	TASK #1430	District #13	2.00
3081	TASK #1466	District #13	2.00
3225	TASK #1466	District #13	1.00
3522	TASK #1466	District #13	2.00
3596	TASK #1540	District #13	2.00
3155	TASK #1540	District #13	1.00
3299	TASK #1540	District #13	1.00

I retired from Alaska Dept of Transportation and built/managed databases. Data was tracked by road segments because roads can cross districts.

[JCabral]

"I retired from Alaska Dept of Transportation and built/managed databases. Data was tracked by road segments because roads can cross districts."
Here in Portugal the same happens, whenever that happens a new segment is created, in this case, a new name is given to the road section.


Regarding the correction of the data, I have to consult the person who gave me the data regarding the districts, regarding the frequency, I think I gave you wrong information, that is, for a normal Route the frequency is the one defined, in the Holiday Route the frequency must always be 1 or 15 in 15 days, that is, this field can also be different for the same "RoadDesignation".
But let me ask you a question, do these fields interfere with the calculation I want, that is, the sum per day of the week of each route for a given OIA?


Tomorrow I will put a new file with the corrections of the districts and confirm the issue of frequencies


Thank you and sorry for these mistakes