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

 

[CaptainD]

Ok,

If understand you right then length and circumfrence are refering to one item (Your looking for items between 3 and 6 whatever long and a circumference between 20 and 40 whatevers)

What are you using to keep the "Lengths" and "Circumferences" together? since you are not using a primary key?

Example, how do you know the two blue highlights are not what is being compared?

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

 

[jpayn05]

Hi CaptainD,

Sorry, I haven't checked the forum much today. I'm not sure I understand your question; [blue]What are you using to keep the "Lengths" and "Circumferences" together?[/blue] I think the answer would be the test code. Every test has a unique code.

As far as a primary key, the data table contains a multi-field key. The combination of Dnumber, TestCode and DataPoint make up the key.

The records you've highlighted in [blue]blue[/blue] in your post would not be used together to calculate an average because they're the results of two different tests or test codes.

I listed the tables involved a few posts back.

Thanks,
Jackie

 

[CaptainD]

Is TestCode a unique ID for each test or is it the code number that links the description.

ie: A code 201 always means a "LZ CIRC"

201 LZ CIRC MM
203 LENGTH MM

If I add a "Parts" table with a unique ID I can do this.

iPartID sPartNumber sDescription
1 A234 - 223 Steel Shaft
2 A234 - 224 Steel Shaft

Run the following select statement to look at these two parts.

SELECT tParts.iPartID, tParts.sPartNumber, TestData.TestCode, TestCode.TestDesc, TestData.DataPoint, TestData.Data, TestCode.UOM
FROM (tParts INNER JOIN TestData ON tParts.iPartID = TestData.fkPartID) INNER JOIN TestCode ON TestData.TestCode = TestCode.TestCode
ORDER BY tParts.iPartID, TestCode.TestDesc DESC;

I get this

iPartID sPartNumber TestCode TestDesc DataPoint Data UOM
1 A234 -223 201 LZ CIRC 1 37.5 MM
1 A234 -223 203 LENGTH 1 5.43 MM
2 A234 -224 201 LZ CIRC 1 4.01 MM
2 A234 -224 203 LENGTH 2 5.5 MM


I know by the unique ID I have the right measurements for the right part.

In your table you have several 201's and 203's

Assuming of course that is important.