query to return recordset

[Tetelestai]

I'm having a mental block on this
I'm trying to get a query to return a recordset that contains PartLength lengths of a certain MaterialWidth where they do not have the same length of a different MaterialWidth, Also a separate query would return duplicate lengths that are present for both MaterialWidth's

Example Data - Material Width will only be the values 3.5 or 5.5

Partlength, MaterialWidth
14.5, 3.5
14.5, 5.5
20.5, 3.5
36.5, 5.5

For width 3.5 the length returned would be 20.5
For width 5.5 the length returned would be 36.5
For Both widths 14.5 would be returned (distinct)

Thanks

[ADezii]

It appears that, for the first Query, you are requesting the MAXIMUM Partlength for a DISTINCT MaterialWidth. Is this correct?

[Tetelestai]

hi,

not maximum, I should have included more example data. it would return all PartLength's of MaterialWidth X that are not found for MaterialWidth Y

adding this data

62, 3.5
72, 3.5
72, 5.5
84, 5.5

For width 3.5 the length returned would be 20.5, 62
For width 5.5 the length returned would be 36.5, 84
For Both widths 14.5, 72 would be returned (distinct)

[ADezii]

Let me regroup on this one.

[ADezii]

I have found a solution to your dilemma, a rather clumsy solution, but a working solution nonetheless. The following Query, utilizing a Calculated Field and a Public Function, will produce a Comma-Delimited String of all PartLengths of MaterialWidth X that re not found for MaterialWidth Y. I'll post the relevant code, test data, SQL, and results below along with the Test Database as an Attachment. Should you have any questions, simply ask.

1. Test Data in tblTest:
   

   PartLength	MaterialWidth
   14.5	          3.5
   14.5	          5.5
   20.5	          3.5
   36.5	          5.5
   62	            3.5
   72	            3.5
   72	            5.5
   84	            5.5
   119	           3.5
   1234	          5.5
   12	            5.5
   221	           5.5

2. Query Definition:
   

   SELECT DISTINCT tblTest.MaterialWidth, fProcessMaterialWidths([MaterialWidth]) AS Return
   FROM tblTest
   ORDER BY tblTest.MaterialWidth;

3. Function Definition:
   

   Public Function fProcessMaterialWidths(sngMaterialWidth As Single) As String
   Dim strSQL As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strBuild As String
   
   'Create a Recordset consisting of all the PartLengths for the 'Passed'
   'MaterialWidth, keeping in mind that there are only 2 Material Widths
   strSQL = "SELECT [PartLength] FROM tblTest " & _
            "WHERE [MaterialWidth] = " & sngMaterialWidth & ";"
            
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
   
   'See if the PartLength exists for the other MaterialWidth, if it
   'doesn't start building a Comma-Delimited String of PartLengths
   With rst
     Do While Not .EOF
       If DCount("*", "tblTest", "[MaterialWidth] <> " & sngMaterialWidth & _
                 " And [PartLength] = " & ![PartLength]) = 0 Then
         strBuild = strBuild & ![PartLength] & ","
       End If
         .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing
   
   fProcessMaterialWidths = Left$(strBuild, Len(strBuild) - 1)
   
   strBuild = ""
   End Function

4. Query Results:
   

   MaterialWidth	Return
   3.5	          20.5,62,119
   5.5	          36.5,84,1234,12,221

5. Download Test Database below:

P.S. - It assumes no NULL Values in either Field, and only 2 DISTINCT MaterialWidths (X,Y).

[Tetelestai]

Thanks, I'll test this out tomorrow!