How to properly join/relate tables in a query to populate fields

[Guest]

I'm building a query to calculate production parameters in a kanban setup. I have a table (let's call it table1) that contains the intervals for each resource (machine) as follows

table1
ID resource interva
1 machine1 0.7
2 machine2 0.2
3 machine3 0.5
4 machine4 0.7

The query is based on a table (table2) that contains production loops where product moves from one resource to the next. The resource where the product moves "from" is the supplier resource (SuppResource) and the resource where the product moves "to" is the user resource (UserResource). Each resource can be either a user or a supplier, depending on the loop

table2
loopID Product SuppResource UserResourc
1 prod1 machine1 machine
2 prod1 machine2 mschine
3 prod2 machine1 machine
4 prod2 machine2 machine

In any given loop, I need to get the interval for both the user and the supplier resource to use in other calculations

query
loopID Product SuppResource UserResource SuppInterval UserInterva
1 prod1 machine1 machine
2 prod1 machine2 mschine
3 prod2 machine1 machine
4 prod2 machine2 machine

My question is what expressions can I use to get the values from the interval field in table1 to populate the SuppInterval and UserInterval fields in the query, based on the respective values of the SuppResource and UserResource fields in table2. And what's the proper way, if any, to join table1 and table2

Any help would be greatly appreaciated
Thanks

 

[Tom Ellison]

Dear MacLean:

It is permissible to join to table1 twice to get this. The necessary
technique is "aliasing" which allows you to enter two or more instance
of the same table in one query.

SELECT T2.Product, T2.SuppResource, T1a.interval AS SuppInterval,
T2.UserResource, T1b.interval AS UserInterval
FROM table2 T2
INNER JOIN table1 T1a ON T1a.resource = T2.SuppResource
INNER JOIN table1 T1b ON T1b.resource = T2.UserResource

The facility of aliasing is indispensable in such cases.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear MacLean:

You do not need 2 instance of table2. If you are using Access Jet for
your database, you do need parentheses around your join, however. I
use the MSDE that comes with Access, so I sometimes forget this.

SELECT T2.Product, T2.SuppResource, T1a.interval AS SuppInterval,
T2.UserResource, T1b.interval AS UserInterval
FROM (table2 T2
INNER JOIN table1 T1a ON T1a.resource = T2.SuppResource)
INNER JOIN table1 T1b ON T1b.resource = T2.UserResource

I think this may help.

However, testing could begin by removing each of the joins and testing
the other:

SELECT T2.Product, T2.SuppResource, T1a.interval AS SuppInterval,
T2.UserResource
FROM table2 T2
INNER JOIN table1 T1a ON T1a.resource = T2.SuppResource

SELECT T2.Product, T2.SuppResource,
T2.UserResource, T1b.interval AS UserInterval
FROM table2 T2
INNER JOIN table1 T1b ON T1b.resource = T2.UserResource

This will tell if something else is broken.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts