Intersect query in Access 1

[jpayn05]

Hello to all,

I'm working with SQL in Access VBA code behind a form. And I'm not an expert in SQL or Access, so please bear with me.
I have a query that gives me an ID (Dnumber), a test type (TestDesc) and several aggregate functions based on raw data stored in a table. The query (from the query view, not VBA) is as follows;

 SELECT TestData.Dnumber, TestCode.TestDesc, Avg(TestData.Data) AS Average, Min(TestData.Data) AS [Min], Max(TestData.Data) AS [Max] FROM TestCode INNER JOIN TestData ON TestCode.TestCode = TestData.TestCode GROUP BY TestData.Dnumber, TestCode.TestDesc HAVING (((TestCode.TestDesc) Like "LENGTH") AND ((Avg(TestData.Data)) Between 3 And 6));

This works. I am currently using a form with a multi-select list box to allow the user to select test type ("LENGTH", above) and 2 text boxes for a low and high search range (3 and 6, above). But what I'd like to do is be able to choose two or more test types and enter different search ranges for each. From searching the Internet it seems that an SQL INTERSECT query might do the trick. However, I also found out that Access does not allow the INTERSECT keyword, but uses joins to simulate an intersection. But I can't seem to find the correct SQL syntax to accomplish this in Access VBA.

I have tried working around this using what I know. I've tried using the UNION keyword and filtering those results. The UNION keyword works fine but I then have trouble filtering the results in VBA code.

Could someone please point me in the right direction? Any assistance will be greatly appreciated.

Thanks in advance,
Jackie

 

[r937]

not sure what you mean by INTERSECT, but try this --

SELECT TestData.Dnumber
     , TestCode.TestDesc
     , AVG(TestData.Data) AS Average
     , MIN(TestData.Data) AS [Min]
     , MAX(TestData.Data) AS [Max] 
  FROM TestCode 
INNER 
  JOIN TestData 
    ON TestData.TestCode = TestCode.TestCode
GROUP 
    BY TestData.Dnumber
     , TestCode.TestDesc 
HAVING (
       TestCode.TestDesc Like "LENGTH" 
   AND AVG(TestData.Data) BETWEEN 3 AND 6
       )
    [blue]OR[/blue](
       TestCode.TestDesc Like "FOO" 
   AND AVG(TestData.Data) BETWEEN 9 AND 37
       )

if that's not what you want, try [blue]AND[/blue] instead of [blue]OR[/blue]

r937.com | rudy.ca

 

[jpayn05]

Thanks for your response. I tried your suggestions in the query window. The OR version worked as I thought. It returned every record that had at least one of the test types within the search ranges. The AND version, which I had hopes for, returned no records. I tried regrouping the HAVING statements inside parenthesis but nothing seemed to work.

I'm using Access 2000 if that makes any difference.

As far as the intersect keyword, I think its an Oracle convention that, based on some criteria, returns records that are common between two or more queries. I've never used it, just read about it online.

Thanks again.

 

[r937]

if the AND version didn't work, perhaps there were no rows that satisfied the conditions you submitted



r937.com | rudy.ca

 

[PWise]

I want to note the following about the having clause

when your criteia is

(TestCode.TestDesc Like "LENGTH"....)
and
(TestCode.TestDesc Like "FOO"....)

this can not return anything beacuse
Like "LENGTH" will only return "LENGTH"
and "LENGTH" will never be = "foo"

try this

HAVING (
       TestCode.TestDesc Like "*LENGTH*" 
   AND AVG(TestData.Data) BETWEEN 3 AND 6
       )
    and(
       TestCode.TestDesc Like "*FOO*" 
   AND AVG(TestData.Data) BETWEEN 9 AND 37
       )

 

[r937]

but of course!! i should have spotted that myself!!



r937.com | rudy.ca

 

[PWise]

ok! you are allowed 1 to many on new years eve


Happy New Year

 

[jpayn05]

Thanks for all your replies.

First of all, for my benefit and knowledge, can you explain the use of the "*" in the HAVING clause. Is it supposed to be a wildcard character?

Second, I still could not get the AND version to return any records. I've also double checked the data table and there are two ID numbers that should be returned based on the search ranges I'm using.

Here's the latest SQL statement;

SELECT TestData.Dnumber, 
             TestCode.TestDesc, 
             Avg(TestData.Data) AS Average, 
             Min(TestData.Data) AS [Min],
             Max(TestData.Data) AS [Max],
             StDev(TestData.Data) AS StDev, 
             (([StDev])/([Average])*100) AS [%CV], 
             Count(TestData.Data) AS [Count]
FROM TestCode INNER JOIN TestData ON TestCode.TestCode = TestData.TestCode
GROUP BY TestData.Dnumber, TestCode.TestDesc, TestCode.TestCode
HAVING (TestCode.TestDesc Like "*LZ CIRC*" AND Avg(TestData.Data) Between 20 And 40) 
and
(TestCode.TestDesc Like "*LENGTH*" AND Avg(TestData.Data) Between 3 And 6);

Again, the "*" seemed not to make any difference. I checked my syntax and could find no obvious (to me) problems.

Thanks again,
Jackie

 

[r937]

would you kindly show the two values of TestCode.TestDesc that satisfy the criterion of containing both LZ CIRC and LENGTH

r937.com | rudy.ca

 

[jpayn05]

Yes, I'll try.

The data table (TestData) stores raw data on different size and shape parts. The parts are given a lot or batch number, Dnumber. So a given Dnumber may be for cylindrical parts for which the part LENGTH, circumference (LZ CIRC) and maybe even individual part weight (WT) are measured and stored in the TestData table. So let's say we have Dnumber 1 and we measure and store 10 part lengths, 6 part circumferences and 10 individual weights. Let's say that Dnumber 5 and 6 are also cylindrical parts which are tested in the same ways as Dnumber 1.

So what I'd like to be able to do is query the database for parts that, say, have a length between 3 and 6 AND a circumference between 20 and 40.

As it stands now, Dnumber 1 and 6 meet these criteria. Dnumber 5 has a length that falls between the length criteria, but its circumference is not between 20 and 40, so I don't want to see it in the query results.

I'm also trying to do this in VBA code behind a form that allows the user to choose which tests and the search ranges for each test selected, to be used in the query.

Hope this makes sense.
Thanks,
Jackie

 

[r937]

yeah, it makes sense, i was hoping you'd show the actual value of the column, but i guess it doesn't matter because i have another idea

maybe it's the difference between DAO and DOA? or is it ADO and AOD? or OAD and ODA?

whatever the case (you can tell i'm not a microsoft programmer), perhaps you could try it with % as the wildcard character instead of *

... Like "[red]%[/red]LZ CIRC[red]%[/red]"

r937.com | rudy.ca

 

[CaptainD]

The way I'm reading this, both criteria have to be met.

HAVING (TestCode.TestDesc Like "*LZ CIRC*" AND Avg(TestData.Data) Between 20 And 40) 
[COLOR=blue]and[/color]
(TestCode.TestDesc Like "*LENGTH*" AND Avg(TestData.Data) Between 3 And 6);

Shouldn't you be looking for data that meets one or the other?

HAVING (TestCode.TestDesc Like "*LZ CIRC*" AND Avg(TestData.Data) Between 20 And 40) 
[COLOR=blue]OR[/color]
(TestCode.TestDesc Like "*LENGTH*" AND Avg(TestData.Data) Between 3 And 6);

 

[jpayn05]

r937,

Sorry, I misinterpreted your earlier post. The column in the TestData table actually contains a test code (i.e. - 203 for LENGTH). That's why I have the inner join; to allow the user to select a test name rather than a code.

Also, the "%" did not work, either. Thanks anyway!


CaptainD,

Actually, I want to use the AND instead of the OR. That way only rows that meet both conditions will be returned. I'd like the user to be able to search the data based on several conditions. Obviously, the more conditions are selected, the chance that no matching records will be found increases.

Thank you,
Jackie

 

[CaptainD]

So a record in TestCode.TestDesc can be both "LZ CIRC" and "Length" at the same time?

OR

Do you have records where TestCode.TestDesc is either "LZ CIRC" or "Length"

Did you try it using OR ?

To me, using "OR" should give you all the records where

TestCode.TestDesc is like "LZ CIRC" and TestData.Data is Between 20 and 40

AND

all the data where

TestCode.TestDesc is like "Length" and TestData.Data is Between 3 and 6

HAVING (TestCode.TestDesc Like "*LZ CIRC*" AND Avg(TestData.Data) Between 20 And 40) 
OR
(TestCode.TestDesc Like "*LENGTH*" AND Avg(TestData.Data) Between 3 And 6);

 

[r937]

okay, i'm going to ask again, because now i'm confused again

could you please show some sample rows of data from each of the tables? all fields in the rows, please

and it would help if the rows of sample data were actually related

i have a feeling you will need a different query completely

r937.com | rudy.ca

 

[jpayn05]

Ok, hopefully this will help.

This is the test code table.

[b][u]TestCode	TestDesc	UOM	Comments[/u][/b]
201	     LZ CIRC	 MM	
203	     LENGTH	  MM	
205	     WEIGHT GT   G	  10 unit weight
211	     WIDTH	   MM	
214	     THICKNESS   MM	
415	     WEIGHT	  G	  Single unit weight

This is a sample of the test data table.

[b][u]Dnumber      TestCode      DataPoint      Data[/u][/b]
1            201           1              38.50
1            201           2              37.75
1            203           1              5.43
1            203           2              5.50
5            201           1              17.50
5            201           2              17.39
5            203           1              4.01
5            203           2              4.01
6            201           1              24.50
6            201           2              24.55
6            203           1              4.53
6            203           2              4.50

Obviously, each Dnumber may have more than 2 data points for each test. However by this data, both Dnumber 1 and 6 satisfy the search requirement of having an average LENGTH(203) between 3 and 6 AND an average LZ CIRC(201) between 20 and 40. Dnumber 5 satisfies the LENGTH spec, but does not meet the LZ CIRC requirement.

I hope I've understood what you've asked.

Thanks,
Jackie

 

[r937]

thanks!

seeing the data, as so often the case, leads directly to identifying the cause of the trouble

in a nutshell: push the AVG and GROUP BY on TestCode.TestCode down into a subquery

SELECT d.Dnumber
  FROM TestCode
INNER
  JOIN ( SELECT Dnumber
              , TestCode
              , AVG(TestData.Data) AS Average
              , MIN(TestData.Data) AS [Min]
              , MAX(TestData.Data) AS [Max]
           FROM TestData
         GROUP
             BY Dnumber
              , TestCode ) AS d 
    ON d.TestCode = TestCode.TestCode
 WHERE TestCode.TestDesc = 'LENGTH'  AND Average BETWEEN 3 AND 6
    OR TestCode.TestDesc = 'LZ CIRC' AND Average BETWEEN 20 AND 40
       )
GROUP
    BY d.Dnumber
HAVING COUNT(*) = 2

r937.com | rudy.ca

 

[CaptainD]

This

SELECT TestData.Dnumber, TestCode.TestDesc, Avg(TestData.Data) AS Average, Min(TestData.Data) AS [Min], Max(TestData.Data) AS [Max], StDev(TestData.Data) AS StDev, (([StDev])/([Average])*100) AS [%CV], Count(TestData.Data) AS [Count]
FROM TestCode INNER JOIN TestData ON TestCode.TestCode = TestData.TestCode
GROUP BY TestData.Dnumber, TestCode.TestDesc, TestCode.TestCode
HAVING (((TestCode.TestDesc) Like "*LZ CIRC*") AND ((Avg(TestData.Data)) Between 20 And 40)) OR (((TestCode.TestDesc) Like "*LENGTH*") AND ((Avg(TestData.Data)) Between 3 And 6));

Gives me

Dnumber	TestDesc	Average	Min	Max	StDev	%CV	Count
1	LENGTH	5.465	5.43	5.5	4.94974746830186E-02	0.905717743513606	2
1	LZ CIRC	37.625	37.5	37.75	0.176776695296637	0.469838392814982	2
5	LENGTH	4.01	4.01	4.01	0	0	2
6	LENGTH	4.515	4.5	4.53	2.12132034352786E-02	0.469838392807943	2
6	LZ CIRC	24.525	24.5	24.55	3.53553390597132E-02	0.144160403913204	2

Is that what you are looking for?

 

[jpayn05]

r937,

That's pretty awesome. I appreciate all your time and effort. I would like to see the results of the aggregate functions returned also. But I'd like to attempt modifying what you've given me on my own. If you'd like to post your solution to this, I'll try not to "peek" until I've given up. Thanks again.


CaptainD,

That's pretty much what I'm looking for, except I want to exclude Dnumber 5 because it's average LZ CIRC value is not between 20 and 40. Thanks anyway.

Jackie

 

[r937]

hint: store the "d" subquery as a stored query (so that we can refer to it more than once in the outer query)

then my query becomes --

SELECT d.Dnumber
  FROM TestCode
INNER
  JOIN d 
    ON d.TestCode = TestCode.TestCode
 WHERE TestCode.TestDesc = 'LENGTH'  AND Average BETWEEN 3 AND 6
    OR TestCode.TestDesc = 'LZ CIRC' AND Average BETWEEN 20 AND 40
       )
GROUP
    BY d.Dnumber
HAVING COUNT(*) = 2

then to show the averages, do this --

[red]select * from d where Dnumber in ([/red]
SELECT d.Dnumber
  FROM TestCode
INNER
  JOIN d 
    ON d.TestCode = TestCode.TestCode
 WHERE TestCode.TestDesc = 'LENGTH'  AND Average BETWEEN 3 AND 6
    OR TestCode.TestDesc = 'LZ CIRC' AND Average BETWEEN 20 AND 40
       )
GROUP
    BY d.Dnumber
HAVING COUNT(*) = 2
[red])[/red]

r937.com | rudy.ca