Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql - using AND in child table (2000/XP)

    Say I have a two table database containing tblStudents and tblGrades. tblStudents is a list of students (forename, surname etc), tblGrades is a child table, with a record for each subject studied by each student, joined to tblStudents by a StudentID field.

    How would I return Students who took say 'Maths' and 'English', not just 'Maths' or just 'English'

    ps. I don't have a sample, this is the best scenario I can think of to describe a problem someone else is having.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    Create a query based on tblGrades.
    Add the StudentID field and the field that identifies the subject.
    Specify "Maths" or its ID in the criteria line for the subject field (depending on how the subject is stored)
    Clear the Show check box for this column.
    Save this query as qryMaths.
    Change the criteria for the subject column to "English" or the corresponding ID.
    Save the query as qryEnglish and close it.
    Create a new query based on tblStudents, qryMaths and qryEnglish.
    Add all fields (or those you need) from tblStudents to the query grid.
    Join tblStudents to each of the queries on StudentID.
    This query will return all students who took both Maths and English.

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    Is there any way that could that be achieved 'on the fly', creating an sql statement in vba?

    The actual problem involves companies and products, the guy wants to allow the user to pick products from a list (multiselect) and return companies that sell them (or something like like - it wasn't entirely clear, hence the student/subjects example). There would potentially be any number of criteria, each therefore requiring their own query , all then being joined - sounds hideous!

    Is this one of those questions that sounds straightforward but isn't?

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    I have attached a sample database that uses trimmed-down tables from the NorthWind database. The form frmDemo has a multi-select list box from which you can select products; clicking the command button opens a report that lists all orders whose order details include all selected products. To test:
    - Select Aniseed Syrup and click the button.
    - View the report, then close it.
    - Select Boston Crab Meat too and click the button again.
    - View the report.
    The selection criteria are displayed in a message box, just as an illustration.

    The code behind the command button is as follows:

    Private Sub cmdReport_Click()
    Dim strWhere As String
    Dim varItm As Variant

    On Error GoTo ErrHandler

    ' Loop through the selected items in the list box
    For Each varItm In Me.lboProducts.ItemsSelected
    ' Add part to strWhere
    strWhere = strWhere & " AND pkeyOrderID In " & _
    "(SELECT pkeyOrderID FROM tblOrderDetails " & _
    "WHERE fkeyProductID=" & varItm & ")"
    Next varItm
    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)
    ' Just for testing
    MsgBox "Criteria: " & strWhere, vbInformation
    End If
    ' Open report with selection criteria
    DoCmd.OpenReport ReportName:="rptOrders", _
    View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    ' Ignore canceling the report
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    Attached Files Attached Files

  6. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    That's it.

    Many thanks <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #6
    New Lounger
    Join Date
    May 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    Thanks

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    Welcome to Woody's Lounge!

    One thing to keep in mind: processing the where-condition will probably be slow if the user selects many items in the list box.

  9. #8
    New Lounger
    Join Date
    May 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    Speed shouldn't be too much of a problem, the database isn't that big.

    The full problem is

    c100 companies working in one of 14 geographical regions, and installing between one and five products.

    the region is selected from a drop down and the products from a list box. I got it working to a point, except it was using OR not AND. This should then display those companies that match the criteria.

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    Feel free to ask for more assistance if you need it.

  11. #10
    New Lounger
    Join Date
    May 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    I am having a bad day, my head has gone completely!

    Some advice, that may help tomorrow.

    I have three tables, one is the companies, includes various details, second is region this has fields company name and region they work in, third is product, this lists company name and products they install

    To get the code above to work i need to aim it at the product table? this should give me a list of company names that install the product.

    To get the region i need to have extended the SQL statement to refer to the region table, this compares the contents of a dropdown with the field contents to give the company name.

    In my head i have a list of companies who install in the region and a list of companies who install the product. which should give me list of companies that do both.

    If my head was in it this might be doable today.

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    In the example I provided higher up in this thread, the goal was to return a list of orders, so the where-condition acts on the orders table. You want to return a list of companies, so you have to create a where-condition that acts on the companies table. The part for the products is similar to the code I posted. You'd need to add another " AND ..." to the strWhere string for the region. Something like

    ...
    ' Did the user select a region?
    If Not IsNull(Me.cboRegion) Then
    ' Start strWhere
    strWhere = " AND CompanyID In " & _
    "(SELECT CompanyID FROM tblRegions " & _
    "WHERE RegionID=" & Me.cboRegion
    End If
    ' Loop through the selected items in the list box
    For Each varItm In Me.lboProducts.ItemsSelected
    ' Add part to strWhere
    strWhere = strWhere & " AND CompanyID In " & _
    "(SELECT CompanyID FROM tblProducts " & _
    "WHERE ProductID=" & varItm & ")"
    Next varItm
    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)
    ...

    You must, of course, substitute the correct names. I have assumed that the various ID fields are numeric.

  13. #12
    New Lounger
    Join Date
    May 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    The code is currently as follows.

    Private Sub cmdDuelBoxFilter_Click()
    On Error GoTo Err_cmdDuelBoxFilter_Click


    'begin the sql string

    strSQL = "select tblregion.company, tblregion.[region of work], tblProduct.products from tblproduct inner join tblregion on tblproduct.company=tblregion.company"

    'Build the IN string by looping through the listbox
    For i = 0 To List2.ListCount - 1
    If List2.Selected(i) Then
    If List2.Column(0, i) = "All" Then
    flgSelectAll = True

    End If
    strIN = strIN & "'" & List2.Column(0, i) & "',"

    End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string

    strWhere = " WHERE (((tblregion.[region of work])=[forms]![frmsearch]![cmbsearch])) <img src=/S/drop.gif border=0 alt=drop width=23 height=23> and (tblproduct.[products]) in (" & Left(strIN, Len(strIN) - 1) & ")" <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
    strSQL = strSQL & strWhere

    End If


    This works in so much as it creates a list of or's. The bit between the smillies is wrong and needs to be modified to reflect the code in your response i assume.

    The loop that creates an IN clause (the bit between the smilies) is creating an OR condition?

    To correct this i need to replace the IN with further WHERE Clauses? This would be thsi section of your code?

    ' Loop through the selected items in the list box
    For Each varItm In Me.lboProducts.ItemsSelected
    ' Add part to strWhere
    strWhere = strWhere & " AND CompanyID In " & _
    "(SELECT CompanyID FROM tblProducts " & _
    "WHERE ProductID=" & varItm & ")"
    Next varItm
    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)

    I assume that VarITM is a numeric value? whereas my cadged code above is using alphanumeric strings for both company name and product name.

    I never claimed to be any good at VBA and just need a little help learning the meanings.
    Attached Files Attached Files

  14. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    The only thing you need in the code I posted if the field is a text field is to change the line

    "WHERE ProductID=" & varItm & ")"

    to

    "WHERE ProductID=" & Chr(34) & varItm & Chr(34) & ")"

    If you'd like more specific help, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions. It's too hard to write the code without seeing the database.

  15. #14
    New Lounger
    Join Date
    May 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sql - using AND in child table (2000/XP)

    Thanks,

    any advice gratefully received, It probably isn't the most elegant of databases, that may be an issue? I spend more time in Excel these days just needed to create something that could filter the results .
    Attached Files Attached Files

  16. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: sql - using AND in child table (2000/XP)

    I'm looking at it, but it'll take some time to straighten things out.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •