• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Query Question

I am trying to create a query to return approved materials per operation.

tblOperations
-operationID
-operation

tblOperationTests
-operationID
-TestID

tblTests
-TestID
-Test

tblProductContact
-MaterialID
-Material
-TestID

I'm drawing a blank....basically, I can have multiple tests assiciated with an operation so:

Operation:    Test:
Fractionation 100% Acetone
Dissolving      Buffer H

then in my tblProductContact I have info like

Material:  Test:                Result:
Rubber     100% Acetone  Pass
Rubber     Buffer H           Fail

I need to know what materials are valid for what operations (all tests are pass)
0
yhwhlivesinme
Asked:
yhwhlivesinme
  • 27
  • 18
  • 6
1 Solution
 
rockiroadsCommented:
try something like this


select o.operation, t.test, p.material
from tblProductContact p, tblTests t, tblOperationTests ot, tblOperations o
where p.TestID = t.TestID
and t.TestID = op.TestID
and ot.OperationID = o.OperationID
0
 
yhwhlivesinmeAuthor Commented:
right so that gives me what materials passed which tests and which of those tests relate to what operation, but what's the next step, how do I find out if a specific material has passed ALL of the required tests?
0
 
rockiroadsCommented:
how is the material/test relation setup

can one material take the same test more than once?

is there a pass/fail flag or are all records assumed to be passed

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
yhwhlivesinmeAuthor Commented:
There is a pass fail flag, it's called Status in tblProductContact  One material would not take the test more than once.  Sorry I didn't explicitly say that before.
0
 
yhwhlivesinmeAuthor Commented:
I was thinking about doing something like:

Operation:    Material:    Test:                Pass:
Fractionation Rubber       100% Acetone  1
Fractionation Rubber       Buffer H           1

then summing them up to make sure they passed the correct number of tests, we're already inner joining them so we know that they passed the correct tests, so if we have the correct numbers then we should be ok......that seems like a bad workaround though thats why I posted the question
0
 
rockiroadsCommented:
this code lists all materials that have passed all tests

SELECT Material
FROM tblProductContact
WHERE Status=1
GROUP BY Material
HAVING Count(*) = DCOUNT("TestID","tblTests")



0
 
yhwhlivesinmeAuthor Commented:
We will never test all of our materials using all of the tests, I will post my sql for what I have that I believe works, I was hoping to find a better way, but I at least need one way that works
0
 
rockiroadsCommented:
save the above as qryAllTests


Now Im writing this query, I think it should work


select o.operation, p.material, t.test, p.status
from qryAlltests q, tblProductContact p, tblTests t, tblOperationTests ot, tblOperations o
where q.Material = p.Material
and p.TestID = t.TestID
and t.TestID = op.TestID
and ot.OperationID = o.OperationID


0
 
yhwhlivesinmeAuthor Commented:
I have 3 queries, qry1, qry2 and qry3 that puts them together....yes I know I will change their names later :)

qry1 basically what we got before except now I'm summing up how many of the given tests it's passed.  qry2 counts how many it should have passed, then qry3 checks to see if how many fit the right amount of criteria

qry1:
SELECT o.OperationID, o.Operation, p.Description, Sum(IIf([Status]="acceptable",1,0)) AS Expr1
FROM tblProductContact AS p, tblTests AS t, tblOperationTests AS ot, tblUnitOperation AS o
WHERE (((p.Contact)=[t].[Test]) AND ((t.testID)=[ot].[TestID]) AND ((ot.OperationID)=[o].[OperationID]))
GROUP BY o.OperationID, o.Operation, p.Description;

qry2:
SELECT tblUnitOperation.OperationID, tblUnitOperation.Operation, Sum(IIf([testID]<>-1,1,0)) AS Expr1
FROM tblUnitOperation INNER JOIN tblOperationTests ON tblUnitOperation.OperationID = tblOperationTests.OperationID
GROUP BY tblUnitOperation.OperationID, tblUnitOperation.Operation;

qry3:
SELECT qry2.OperationID, qry2.Operation, qry1.Description
FROM qry1 INNER JOIN qry2 ON (qry1.Expr1 = qry2.Expr1) AND (qry1.OperationID = qry2.OperationID)
GROUP BY qry2.OperationID, qry2.Operation, qry1.Description;
0
 
yhwhlivesinmeAuthor Commented:
I'll try your way and get back to you
0
 
yhwhlivesinmeAuthor Commented:
just looking over your query it wont work, you cannot do: q.Material = p.Material because we will not test all of our materials on all of our tests
0
 
rockiroadsCommented:
ok, that last query was based on the question u said - how do I find out if a specific material has passed ALL of the required tests? I just assumed the required tests are those in the tests table

0
 
yhwhlivesinmeAuthor Commented:
test ID  Test
1          100% Acetone
2          Buffer H
3          Water For Injection

OperationID Operation
1                 Fractionation
2                 Dissolving/Filtation

OperationID TestID
1                 2
1                 3
2                 1
2                 2

something like the above is what will happen, we will have a list of tests and a list of operations, only some tests apply to some operations, so as listed above Fractionation doesn't requre a 100% acetone on all of it's materials and Dissolving/Filtration doesn't need a Water for Injection test on it's materials
0
 
rockiroadsCommented:
Sorry, but Im not quite understanding what you want now,

What exactly do u want listed? what Ive done so far is listed those that are linked in all tables and listed those that passed all tests, but this isnt what your after
0
 
harfangCommented:
Hello yhwhlivesinme

I have been trying to solve this, and it seems rather simple. In plain English:

 • Select all operations and associated materials,
 • remove materials where a test is missing,
 • remove materials that have failed a test.

That is a simple query with two subqueries as criteria. Not trivial but simple.

However, you make it impossible for me to see through.

 – tblOperations has vanished
 – replaced by tblUnitOperation, presumably
 – MaterialID and Material are defined but never used (tblProductContact)
 – In the sample, a field "Result" drops in (tblProductContact)
 – Fields Contact and Description pop up (tblProductContact)
 – An orphan field 'Status' seems important (the previous "Result"?)
 – tblProductContact.TestID is not used to create the link with tblTests
 – A text link Contact<->Test is used instead???
 – Is there a table Products?
 – Where is the table Materials?

You did want a list of materials, right?
I sincerely tried to setup your tables, but I failed.

You could try to upload your test database to www.ee-stuff.com/Expert or show us your *real* table structures.

Cheers!
(°v°)
0
 
yhwhlivesinmeAuthor Commented:
harfang,

thanks for the analysis, yes I simplified things in my initial statement.

-tblOperations = tblUnitOperations (sorry for the mixup)
-MaterialID = tblProductContact.MatID
-Material = tblProductContact.description
-tblProductContact.status is the flag for if it passed the test or not
-tblProductContact.Contact is the name of the test that was executed
          (yes I know it would be better to have a test ID, but it's being exported from another database)
-tblProductContact.Description is the name of the Material (as stated above)
-You are correct with the link Contact<-->Test, it's the only way, but our test table is built based on this table so they will always be named the exact same.
-no there is not a table products
-tblProductContact is a table of materials

I will upload my database, but I have to remove all of the data, the data is confidential.
0
 
yhwhlivesinmeAuthor Commented:
0
 
yhwhlivesinmeAuthor Commented:
>You did want a list of materials, right?

I want a list of materials (from tblProductContact) that satisfy the tests associated with a certain operation.  So Basically I want:

Fractionation
-Stainless Steel
-Vulcanized Rubber
-Polyethylene

where Fractionation would be the unit operation and the list under it are the materials approved for it
0
 
rockiroadsCommented:
In the DB u have given, what is the testid in tblProductContact?
0
 
rockiroadsCommented:
Ive got this, but I dont know the link from tblProductContact to Test


SELECT p.MatID, p.Description, t.Test, p.Status
FROM tblUnitOperation AS u, tblOperationTests AS ot, tblTests AS t, tblProductContact AS p
WHERE u.OperationID = ot.OperationID
and ot.TestID = t.TestID
order by 2,3


0
 
yhwhlivesinmeAuthor Commented:
unfortunately there is no TestID in that table, the table is pulled out of another database and they apparently don't have a testID to go with it.  We link based on the text field tblProductContact.Contact = tblTests.Test.  we will most likely build the test table from tblProductContact.Contact anyways, so we should be all set
0
 
rockiroadsCommented:
ok then

now let me get this straight

for a given (or all) operation
u want to list the tests for that and what materials associated with that test?

That is my understanding from what u said


SELECT p.MatID, p.Description, t.Test, p.Status
FROM tblUnitOperation AS u, tblOperationTests AS ot, tblTests AS t, tblProductContact AS p
WHERE u.OperationID = ot.OperationID
and ot.TestID = t.TestID
and p.Contact = t.Test
order by 2,3


what is a pass, is that acceptable?


SELECT p.MatID, p.Description, t.Test, p.Status
FROM tblUnitOperation AS u, tblOperationTests AS ot, tblTests AS t, tblProductContact AS p
WHERE u.OperationID = ot.OperationID
and ot.TestID = t.TestID
and p.Contact = t.Test
and p.Status = "Acceptable"
order by 2,3
0
 
yhwhlivesinmeAuthor Commented:
>for a given (or all) operation u want to list the tests for that and what materials associated with that test?
No, I want a list of materials that satisfy ALL of the tests, I can write the basic query that will join the tables together, but what I was having trouble with was how to make sure they satisfied all of the critieria

>what is a pass, is that acceptable?
yes "acceptable" indicates a pass

0
 
rockiroadsCommented:
In the sample data, there is no material that has passed all tests,

I am assuming then that a pass of all tests is one where all test's defined in tblTest exist in tblProductContact for one material
0
 
rockiroadsCommented:
Am I right in saying in your tblProductContact table

for this material


the duplicate exists but with a different test (and result)
and this is the same for all tests

e.g.

ID    MatID    OldCN    MCM      Desc    Contact    Status
1     924        2896     MC312   Plastic  Acetate    Acceptable
2     924        2896     MC312   Plastic  Acetone    Acceptable


0
 
rockiroadsCommented:
Assuming then that the Description remains the same for one material


SELECT Description
FROM tblProductContact
WHERE Status='Acceptable'
GROUP BY Description
HAVING Count(*) = DCOUNT("TestID","tblTests")


returns all those materials that have passed the tests

do u agree so far?


save this as qryPassAllTests


then we could use this to get the other info?

select o.operation, p.description, t.test, p.status
from qryPassAlltests q, tblProductContact p, tblTests t, tblOperationTests ot, tblUnitOperation o
where q.Description = p.Description
and p.Contact = t.Test
and t.TestID = ot.TestID
and ot.OperationID = o.OperationID




0
 
yhwhlivesinmeAuthor Commented:
When I said ALL tests I meant all tests that are applicable, tblOperationTests defines what tests are required for what unit operations.

yes you are correct in saying that there will be multiple instances of materials in tblProductContact
0
 
rockiroadsCommented:
ok,

this query returns the results of passed tests

SELECT p.Description
FROM tblProductContact p, tblTests t, tblOperationTests ot, tblUnitOperation o
WHERE p.Contact = t.Test
AND t.TestID = ot.TestID
AND ot.OperationID = o.OperationID
AND p.Status='Acceptable'
AND o.Operation = [Enter Operation]
GROUP BY Description


Are we getting closer?
You get prompted for an Operation e.g. Fractionation
0
 
yhwhlivesinmeAuthor Commented:
on first glance it does look like it works, but you have to take into account that a unit operation can have multiple tests associated with it eg:

Fractionation:
Acetone
Buffer H

if I run your query it returns all materials that have passed Acetone and all materials that have passed buffer H
I need all materials that have passed both
0
 
yhwhlivesinmeAuthor Commented:
my 3 queries that were posted above return the correct data, but in my opinion it is a very bad way to do it, I'm looking for a better way, try running my 3rd query and getting data to match those results
0
 
rockiroadsCommented:
Im just trying to break it down first, to try understand what u want
Im on/off this as Im still at work
0
 
yhwhlivesinmeAuthor Commented:
ok cool.

if I haven't conveyed it properly yet, I am trying to get a list of materials that are valid for a unit operation.

Unit operations have tests associated with them, a material must pass all of the tests associated with a unit operation before it can be approved for that unit operation.

ie:

if stainless steel has only passed an acetone and water for injection test, but Fractionation requires an acetone test and a buffer H test, then Stainless steel cannot be approved for Fractionation
0
 
rockiroadsCommented:
ok, this is what I have done

I made a query that joined the tables together and made a count of all passes for a operation

Note, Ive modded the data

SELECT p.Description, o.operation,count(p.Description) as Countit
FROM tblProductContact p, tblTests t, tblOperationTests ot, tblUnitOperation o
WHERE p.Contact = t.Test
AND t.TestID = ot.TestID
AND ot.OperationID = o.OperationID
AND p.Status='Acceptable'
group by p.Description,o.operation

this produced

Description      operation      Countit
Plastic, Teflon, PTFE or TFE      Dissolving/Filtration      2
Plastic, Teflon, PTFE or TFE      Filling      2
Plastic, Teflon, PTFE or TFE      Fractionation      4
Wow      Dissolving/Filtration      1
Wow      Filling      1
Wow      Fractionation      1



I now needed a query that listed the number of tests per operation,  your qry2 does that

SELECT tblUnitOperation.OperationID, tblUnitOperation.Operation, Sum(IIf([testID]<>-1,1,0)) AS SumOfTests
FROM tblUnitOperation INNER JOIN tblOperationTests ON tblUnitOperation.OperationID = tblOperationTests.OperationID
GROUP BY tblUnitOperation.OperationID, tblUnitOperation.Operation;

result was


OperationID      Operation      SumOfTests
1      Fractionation      4
2      Dissolving/Filtration      2
3      Filling      2




Now my third query joins the two together

select distinct q1.Operation, q2.Description, p.Contact
from qryOpTests as q1, qryPassAllTests as q2, tblProductContact p
where q1.SumOfTests = q2.Countit
and q2.Description = p.Description


and this produced this

Operation      Description      Contact
Fractionation      Plastic, Teflon, PTFE or TFE      Acetate
Fractionation      Plastic, Teflon, PTFE or TFE      Acetate/Acetate Buffer
Fractionation      Plastic, Teflon, PTFE or TFE      Acetic Acid
Fractionation      Plastic, Teflon, PTFE or TFE      Acetone



May not be the elegant query you want, but try it and see if it works.
if it does not work then let me know whats gone wrong, but if it works fine, then I leave it up to you, whether you wait for a better query


0
 
yhwhlivesinmeAuthor Commented:
that's pretty much the same as my qry1 qry2 and qry3.  I'll wait on harfang to see if he has any insight...
0
 
rockiroadsCommented:
ok, but did it work? I setup test data and it seems to

I based mine on counts,

List the passed counts per operation from productcontacts (qryPassAllTests)
List the amount of tests per operation (qryOpTests)

If any counts are the same, then u know u have a material that has passed all tests

that is how I based my logic

0
 
yhwhlivesinmeAuthor Commented:
correct, you based yours on counts, and that's basically what I did as well, I said:

if it's acceptable return a 1 if it's not return a zero, then I summed it up which is basically a count.  Yes your query works, but again I was looking for a more elegant way of doing things, the queries seem cumbersome.
0
 
rockiroadsCommented:
ok no probs. Lets see what harfang comes up with
dont take this as disrespectful, but perhaps the queries are like this due to the nature of the design and the request


0
 
yhwhlivesinmeAuthor Commented:
Definately could be true, I am still in the design phase so if you have any suggestions please let me know, the constraints that I have are with tblProductContacts, that is an imported table and cannot be changed as it will be imported every so often.
0
 
rockiroadsCommented:
tblProductContact is the table that could do with normalising but like u said, it cant be done. If it was normalised, but it make your queries a little better looking perhaps.

The other tables look okay, op to test is many to many and that has been broken down
0
 
yhwhlivesinmeAuthor Commented:
thanks, I designed all the other tables (if you're looking at the database, all the tables with the tblwhatever naming convention) another guy designed the other ones....ahhhh the joys of working with other database designers :)  Thanks for your input on this question by the way, I know I had trouble stating what I wanted b/c I made some bad assumptions, so thanks for sticking through it
0
 
harfangCommented:
Hello yhwhlivesinme

I was away for a little, but I now have to keep my promise ;)
(thank your for the upload and the clarifications)

There are three things to notice in your table structure:

1) There no table tblMaterials or tblProducts. This is not good. Even if you import the data, you should run an append query to have your own list of unique materials, or you will have to keep your original solution involving counting test results.

2) You are using tblTests.Test as a key field, since it's the relation field with tblProductContact. This field needs a unique index (and tblProductContact.Contact should be indexed as well).

3) No relationships are defined. Again, you can just use informative joins (i.e. without referential integrity), but the joins should be there, if only to help you create the queries.


You can then start designing a "real" query.

a) Get a list of all operations and associated materials.

    SELECT DISTINCTROW tblUnitOperation.*, tblMaterials.*
    FROM <your five tables, linked in a chain>

This says: show me all operations and materials that have a connection (though the three other tables), and will be the base of the query.


b) Remove any MatID/OperationID combination for which one test has failed

    SELECT MatID, OperationID
    FROM <linked tables tblOperationTests, tblTests, and tblProductContact>
    WHERE Status Not Like 'Accept*';

Note that we need all three tables, or we would produce only a list of materials that have failed any test, which is not what we want.


c) Find the MadID/OperationID combinations for which at least one test is missing.

That is a little harder, as SQL does not work well with "virtual" data, e.g. "missing" rows.

    SELECT DISTINCTROW tblOperationTests.OperationID
    FROM
        tblTests
        INNER JOIN tblOperationTests
        ON tblTests.testID = tblOperationTests.TestID
    WHERE Test Not In (
        Select Contact
        From tblProductContact
        Where MatID = tblMaterials.MatiD
        );

This will actually prompt for tblMaterials.MatID, as this table is not included in the query. It will however create a subquery containing only the tests for one material and then show all tests missing in the subquery.


d) Throwing everything together...

    SELECT DISTINCTROW
        tblUnitOperation.*,
        tblMaterials.*
    FROM (((
        tblUnitOperation
        INNER JOIN tblOperationTests AS OT
        ON tblUnitOperation.OperationID = OT.OperationID)
        INNER JOIN tblTests AS T
        ON OT.TestID = T.testID)
        INNER JOIN tblProductContact AS PC
        ON T.Test = PC.Contact)
        INNER JOIN tblMaterials
        ON PC.MatID = tblMaterials.MatID
    WHERE
        Not Exists (
           SELECT True
            FROM (
                tblTests  T
                INNER JOIN tblOperationTests  OT
                ON T.testID = OT.TestID)
                INNER JOIN tblProductContact  PC
                ON T.Test = PC.Contact
            WHERE
                OT.OperationID = tblUnitOperation.OperationID
                And PC.MatID = tblMaterials.MatID
                And PC.Status Not Like 'Accept*'
            )
        And Not Exists (
            SELECT True
            FROM
                tblTests T
                INNER JOIN tblOperationTests OT
                ON T.testID = OT.TestID
            WHERE
                Test Not In (
                    Select Contact
                    From tblProductContact
                    Where MatID = tblMaterials.MatiD
                    )
                And OT.OperationID = tblUnitOperation.OperationID
            )
    ORDER BY
        tblUnitOperation.OperationID,
        tblMaterials.MatID;


There you go ;)

This implements what I announced earlier:

 • Select all operations and associated materials,
 • remove materials that have failed a test,
 • remove materials where a test is missing.


See also: https://filedb.experts-exchange.com/incoming/ee-stuff/178-Q_21875307.zip
Pay attention to the relationships window and to the indexes in the tables.

For example, there is now a  unique index on the combination MatID/Contact, because you cannot pass the same test twice. If you could, the above query would not accept them anyway, because it looks for any failure for each MatID/OperationID combination...

Cheers!
(°v°)
0
 
harfangCommented:
Come to think of it... buth subqueries can be combined, it would make things even more simple. After all, a missing test and a failed test are almost the same, no? At least we can treat a failed test like a missing test ;)

    WHERE
        Not Exists (
            SELECT True
            FROM
                tblTests T
                INNER JOIN tblOperationTests OT
                ON T.testID = OT.TestID
            WHERE
                Test Not In (
                    Select Contact
                    From tblProductContact
                    Where MatID = tblMaterials.MatiD
                        And Status Like 'Accept*'
                    )
                And OT.OperationID = tblUnitOperation.OperationID
            )

This has a subtle difference with the previous two subqueries, namely that it now would accept a combination if the same test was performed several times and passed at least once. The previous solution would reject  it in that case...

However, you said the same test exists only once for each material, so the difference is irrelevant.

Good luck!
(°v°)
0
 
yhwhlivesinmeAuthor Commented:
thanks for the upload, I'm going to have to analyze that query, it's like a freaking essay :)  leaving work now will look at it in the morning and award points accordingly, will most likely split some way, however if yours works I will definately award the most to you, thank you both for all your hard work
0
 
yhwhlivesinmeAuthor Commented:
harfang,

amazingly enough, your query does give the same results as mine, I spent some time in my database splitting out everything into two tables and assigning relationships (both things I was planning on doing anyways)  I will keep checking yours out to make sure it will work in all situations, but as far as I can see right now it works great!
0
 
harfangCommented:
I'm glad it did. Thinking in "SQL" does not come naturally, and one often has to throw the logic around a bit, but it is the language of choice for this type of questions. After you have studies the query a while (and each subquery), you will find that it's almost readable as plain english:

" OK, here I select some fields from those linked tables, but not where records exists in (here I try to find at least one record which ...), and ..."

BTW: did you subsitute the "simplified" WHERE clause?

(°v°)
0
 
yhwhlivesinmeAuthor Commented:
harfang, thanks for all your hard work, I've been working with sql for 2.5 years now and have written some pretty complex queries and subqueries anywhere from crosstab queries, to multiple subqueries to massive union queries, but couldn't figure this out, that is by far the most complex query that I've seen.  Would you mind explaining some of the aspects to me?

1. "Exists"....haven't used this one yet, i've used in and not in, but never knew exatly what exists evaluated, can you explain?

2. "SELECT True" why do you use true, couldn't you just use any string or whatever? is it just because true is simple?

3. your naming convention for tables, why do you use aliases on all of your tables? do you always do that?
0
 
yhwhlivesinmeAuthor Commented:
forgot to say, yes I did use the simplified where clause
0
 
harfangCommented:
Thank you. I do like these complex queries. If the table structure is sound (and yours is as good as it can be given the restrictions) you can write some pretty neat questions. So once you made the effort to sanitize and upload your data, I was happy to dive into it ;)

1. Exists simply tests whether the subquery returns any records. You could do the same with a left join and then testing if the link field is Null, but a link can have other side-effects. There used to be a very good help page on "subqueries", but I can't find it. There a succinct page in the DAO, ADO, and JetSQL help files, though.

2. Since Exists tests for records, the Select clause is probably removed by the query optimizer. In this situation, many authors use "select 1" (to suggest "any one"), or "select *" (perhaps by analogy with Count(*)), I often use "select true" (which reads a little like "the selection is true").

Now that you ask, I probably should have used "Select 'any failed test'" and "Select 'any missing test'", respectively, just for fun. In fact, it would be meaningful to use this rare chance to sneak a comment into an SQL sentence ;)

3. When I let the QBE do my queries, I usually prefer to leave the original table names. Subqueries, I normally type directly, so that the saving of keystrokes and more importantly the readability of the query become desirable.

What's more, you often use in the subquery tables that already appear in the main query. In that case, it's sometimes vital to alias a table. I guess I just took the habit to alias even if not strictly needed.

Feel free to ask if you have more questions!

(°v°)
0
 
yhwhlivesinmeAuthor Commented:
harfang, thanks for the effort, really helped me out on this one, I love learning these new sql ideas, while I could have done it with my 3 queries, I much prefer your way, and now I can use it in the future!  points to you!!!
0
 
harfangCommented:
Thanks! As I said, I always have fun with these, so it was my pleasure.
Success with your project!
(°v°)
0
 
yhwhlivesinmeAuthor Commented:
got another issue with my database harfang, thought you might be able to help:

http://www.experts-exchange.com/Databases/MS_Access/Q_21880781.html
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 27
  • 18
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now