By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

query to return recordset

P: 34
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
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,607
It appears that, for the first Query, you are requesting the MAXIMUM Partlength for a DISTINCT MaterialWidth. Is this correct?
Apr 4 '10 #2

P: 34
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
Expert 5K+
P: 8,607
Let me regroup on this one.
Apr 5 '10 #4

ADezii
Expert 5K+
P: 8,607
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, 63 views)
Apr 5 '10 #5

P: 34
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.