Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Question Unanswered: SQL Query - throughout multiple tables

    Hello I'm currently working on a practice DB in MS Access.
    And I've just got stuck trying to resolve one query for a combo box.
    Here is a picture of my database to give a view on what I'll be talking about and asking.

    http://img541.imageshack.us/img541/2566/relationsv.png

    I have a Form for adding new orders and in this form
    at one point I select a car by the cars license plate number (car.car_lp) from a combo box.
    Now there is another combo box for which I need the query.
    Into this second combo box I need to select only those drivers (driver.dri_fname ; driver.dri_lname)
    who have that kind of driving license that's needed to drive the previously selected car.

    Now the logical order is to look at the selected cars license plate
    in the 1st combo box then look it on in the car table.
    Next you know what car type it is (car.car_type_id) so you can check what driving license is required
    for that kind of car in the car_type table. And now you would only select those drivers who have
    the driving license class that's required for the selected car.

    Now here is where the query should come.
    Code:
    Private Sub Combo1_AfterUpdate()
    Combo2.RowSource = *QUERY*
               
    End Sub
    If anybody has any idea how this query should look like, please post it.

    Thank you for reading this and hopefully for replying as well.

    James.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, James,

    I wouldn't bother with a second combobox. I would simply make a query with a parameter in the license-plate field, and work out the query in Access' query designer. You have an impressive relationship worked out. Use it, just the way you show it, to run the query from VBA, inputting the parameter programatically to obtain the needed info.

    Eg., in the top of the query designer form, join car (working from left to right) to car_type to dl_class to jt_driver_dl to driver; inner joins all the way. In the Query By Example grid (the bottom of the designer form), all you need is the license plate field with a parameter such as [enter license plate], and the two name fields you're looking for.

    Of course, if you need other fields, such as the type of car the driver has, or other info, you can always add it into the grid also.

    The only reason I could see to use two comboboxes for this problem is if you're doing this for class and the teacher insists on a two-combobox scheme; and even then you can use the above approach. Just put the same query into the second cb's Row Source.

    Sam

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Quote Originally Posted by Sam Landy View Post
    Hi, James,

    ...

    Eg., in the top of the query designer form, join car (working from left to right) to car_type to dl_class to jt_driver_dl to driver; inner joins all the way. In the Query By Example grid (the bottom of the designer form), all you need is the license plate field with a parameter such as [enter license plate], and the two name fields you're looking for.

    ...

    Sam
    I've just tried this:
    Code:
    Private Sub Combo27_AfterUpdate()
    Combo2.RowSource = "SELECT [driver].[dri_lname] FROM [driver]" & _
                "WHERE [car].[car_lp] = '" & Combo27.Value & "' AND [car].[car_type_id] = [car_type].[car_type_id] AND [car_type].[req_dl] = [JT_driver_dl].[dl_class]"";"
               
    End Sub
    And it won't work for some reason.
    And yeah #1 the lecturer requires is this way.
    #2 I kind of like it this way/ It's pretty intuitive.
    #3 I'm not that skilled in MS Acces / SQL so I'm not even bale to do it any other way.

    If you would have any other idea please let me know.
    Thank you for your reply.

    James.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no association between table driver (the table you are selecting data from and the other tables in your where clause

    If I were you I'd stuff the car_type into the combo box (along with the licence plate & other details)

    then I'd pull the cartype from the combo box selected item (its going to be .column(x) where X is the n-1 of the column in th ecombo box. so if cartype was the first column it would be column(0). that saves you a join

    BTW if you are joining tables then you are better off learnign the JOIN syntax, rather than the older deprecated theta syntax.

    so then you need to find drivers who are entitled to drive that class of vehicle
    so you need to find all drivers whose drivers licence is the same as the licence class
    thats is

    Select D.dri_ID from Drivers as D
    JOIN jt_Driver_dl as DL on DL.dri_id = D.dri_ID
    join Car_Type as CT on CT.reg_DL = DL.dl_Class
    where CT.car_type_id = '" & mycombobox.selected.column(x) & "'"

    if car_type_id is numeric then drop the '

    Im not to certain of the code to fid the nth column of the selected itme in a combo or list box.

    Access JOIN syntax is a little non standard so you may need to fiddle with the aboce

    bear in mind you don't need to go through every table to get to where you want. in this case you don't need to join to dl_class as it doesn't give you anythign so you cna join on the common item dl_class

    you can probably create this query directly in the query browser
    create a new query
    add the car_type,jt_driver and driver tables
    add a link between car_type and jt_driver on car_type.req_dl and jt_driver_dl.dl_class
    add the driver ID and fname & lname tot he query output
    and (for now) add the car_type_id as a hidden column but the filter = to a value you know
    then run the query
    once the query works then either copy the SQL from the SQL view or just save the query and use that as the combo's row source. whatever else you do do you will need to tweak the design to refer to the combo box selected column (or however you retrieve that piece of information

    As a final comment, I appreciate this is, or is one of, your first db. not a problem with that. however I'd suggest you develop a naming convention and stick to it
    as part of that drop prefixing the name of the table in that tables column
    eg dri_ in driver
    use ID or TYPE if the column is the PK
    fer instance call it ID in table driver
    but when referring to it in another table prefix it there
    eg
    driver_ID
    but be consistent
    you have not been with table dl_class
    give the tables and columns something that is meaningful without being a mouthful
    in table car_type the table name and (what I presume is) the description are given the same name
    some people suggest that the table names should be the plural of what the table contains

    eg table drivers contains details of drivers

    so when referring to this in a query drivers.id self documents waht the column is by the query prefixing that information as opposed to driver.dri_id
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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