469,091 Members | 1,191 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

query to return recordset

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
Expand|Select|Wrap|Line Numbers
  1. Partlength, MaterialWidth
  2. 14.5, 3.5
  3. 14.5, 5.5
  4. 20.5, 3.5
  5. 36.5, 5.5
  6.  
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
Apr 4 '10 #1
5 2134
ADezii
8,800 Expert 8TB
It appears that, for the first Query, you are requesting the MAXIMUM Partlength for a DISTINCT MaterialWidth. Is this correct?
Apr 4 '10 #2
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

Expand|Select|Wrap|Line Numbers
  1. 62, 3.5
  2. 72, 3.5
  3. 72, 5.5
  4. 84, 5.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)
Apr 4 '10 #3
ADezii
8,800 Expert 8TB
Let me regroup on this one.
Apr 5 '10 #4
ADezii
8,800 Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. PartLength    MaterialWidth
    2. 14.5              3.5
    3. 14.5              5.5
    4. 20.5              3.5
    5. 36.5              5.5
    6. 62                3.5
    7. 72                3.5
    8. 72                5.5
    9. 84                5.5
    10. 119               3.5
    11. 1234              5.5
    12. 12                5.5
    13. 221               5.5
  2. Query Definition:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblTest.MaterialWidth, fProcessMaterialWidths([MaterialWidth]) AS Return
    2. FROM tblTest
    3. ORDER BY tblTest.MaterialWidth;
  3. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fProcessMaterialWidths(sngMaterialWidth As Single) As String
    2. Dim strSQL As String
    3. Dim MyDB As DAO.Database
    4. Dim rst As DAO.Recordset
    5. Dim strBuild As String
    6.  
    7. 'Create a Recordset consisting of all the PartLengths for the 'Passed'
    8. 'MaterialWidth, keeping in mind that there are only 2 Material Widths
    9. strSQL = "SELECT [PartLength] FROM tblTest " & _
    10.          "WHERE [MaterialWidth] = " & sngMaterialWidth & ";"
    11.  
    12. Set MyDB = CurrentDb
    13. Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    14.  
    15. 'See if the PartLength exists for the other MaterialWidth, if it
    16. 'doesn't start building a Comma-Delimited String of PartLengths
    17. With rst
    18.   Do While Not .EOF
    19.     If DCount("*", "tblTest", "[MaterialWidth] <> " & sngMaterialWidth & _
    20.               " And [PartLength] = " & ![PartLength]) = 0 Then
    21.       strBuild = strBuild & ![PartLength] & ","
    22.     End If
    23.       .MoveNext
    24.   Loop
    25. End With
    26.  
    27. rst.Close
    28. Set rst = Nothing
    29.  
    30. fProcessMaterialWidths = Left$(strBuild, Len(strBuild) - 1)
    31.  
    32. strBuild = ""
    33. End Function
  4. Query Results:
    Expand|Select|Wrap|Line Numbers
    1. MaterialWidth    Return
    2. 3.5              20.5,62,119
    3. 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).
Attached Files
File Type: zip Query to Return Recordset.zip (16.9 KB, 73 views)
Apr 5 '10 #5
Thanks, I'll test this out tomorrow!
Apr 6 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Adrian Parker | last post: by
6 posts views Thread by Nicolae Fieraru | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.