Query between two tables

JCabral

New Member
Joined
Feb 11, 2017
Messages
13
HI
I needed some help to make a query, to two tables that I have in an access file, which I only use to store data.
The query is done from Excel, but my difficulty is how to define the statement in SQL, since I'm not very knowledgeable about it either.

So that's it, I have two tables, tblTasks and tblRoutes.

In tblTasks I have the data referring to each Task, namely who does it (OIA), its designation (TASK DESIGNATION) and the extension of that task (INSPECTION EXTENSION).

In the table tblRoutes I have the data for each OIA to do during the week, that is, I have for a given OIA the name of the route it has to do (ROUTE) and what tasks it has to do on Monday, Tuesday, Wednesday, Thursday and Friday.

Important: The tables must have this format and I cannot change them.

The query I need to do is; for a given OIA and for a given Route I need to know the inspection extension (INSPECTION EXTENSION) carried out for each day of the week.

NOTE: I don't know if it's possible to put my access file here?

Thanks
 

Attachments

  • tblRoutes.PNG
    tblRoutes.PNG
    8.5 KB · Views: 8
  • tblTasks.PNG
    tblTasks.PNG
    9.7 KB · Views: 9
  • tblRoutesDATA.PNG
    tblRoutesDATA.PNG
    28.2 KB · Views: 10
  • tblTasksDATA.PNG
    tblTasksDATA.PNG
    27.3 KB · Views: 9

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Your routes table does not appear to be normalised.
Regardless, unless you can join them in some way, you are out of luck.
I am not sure OIA is going to work?, perhaps Goup on several fields?
 
Upvote 0
This is a classic example of how trying to work with tables that are not normalized can make what should be "simple tasks" extremely difficult to do.
Working with data that is not normalized can be a HUGE pain!
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I didn't put the link to the other post here, because it was pending on the other side, if you saw the post on the other side there was a link to this post.
Thank you very much for your answer
 
Upvote 0
I don't have time to study this (leaving in minutes) but I suspect Union queries will be the only way.
 
Upvote 0
I don't have time to study this (leaving in minutes) but I suspect Union queries will be the only way.
Yeah, that is usually the solution to issues like this - do a separate query for each day of the week, and use "UNION" to see each sub-query together.
Just note that the results of UNION queries are NOT updateable.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top