01-09-13, 18:02 #1Registered User
- Join Date
- Jan 2013
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.
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.
Private Sub Combo1_AfterUpdate() Combo2.RowSource = *QUERY* End Sub
Thank you for reading this and hopefully for replying as well.
01-09-13, 20:29 #2Registered User
- Join Date
- May 2004
- New York State
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.
01-10-13, 05:11 #3Registered User
- Join Date
- Jan 2013
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 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.
01-10-13, 05:55 #4Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
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
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_idI'd rather be riding on the Tiger 800 or the Norton