Query between two tables

JCabral

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.
Sort by date Sort by votes
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
Joe4 said:
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.
Click to expand...

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
Micron said:
I don't have time to study this (leaving in minutes) but I suspect Union queries will be the only way.
Click to expand...
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
You must log in or register to reply here.

Similar threads

C
  • Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
180
welshgasman
welshgasman
M
  • Solved
Show fields in one table of two tables
Replies
2
Views
259
welshgasman
welshgasman
K
  • Solved
Struggling with Editting a "search" query
2 3
Replies
22
Views
1K
welshgasman
welshgasman
JenniferMurphy
  • Question
How to structure a variable product rating database
Replies
5
Views
422
mdmilner
M
H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
441
heathball
H

Forum statistics

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

Share this page

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