Suggestions for Designing Hierarchical Tables

[Tamrak]

Good afternoon all,

I have been assigned to create the database for a project. There are several things involved. To make it easy to understand what I try to accomplish, please think of it this way:

1. Create a table name “vehicle”
2. Under vehicle type, there will be “car”, “truck”, “mini-van”, “SUV” etc.
3. Under each type, there will be a brand, such as “Toyota”, “Nissan”…etc.
4. Under each brand, let’s say -> “Toyota”, it will be the model, such as “Camry”, “Corolla”, “Prius” etc.
5. Under each model, let’s say “Corolla”, it will be the style, such as “coupe”, “S” … etc.

My questions are the following:

1. Should the tables be created in five or six separate tables?
2. What do you suggest the primary key or the naming convention to be in constructing the relationships?
3. When the constructions of tables are completed, the data entry will follow. I believe that the form will have a lot of “unbound” fields (drop-down.) What I am looking are:
a. When the user select the type, let’s say -> car, all the brands associated with the car will be given on the list. No “truck” should be listed. How can I control something like this?
b. Do you recommend creating the data entry form like this, based on a query?

If you have seen a post similar to this one, please kindly send me a link. I would like to obtain the ideas of what to do. Thank you for your time.

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.tek-tips.com/faqs.cfm?fid=4289

http://www.tek-tips.com/faqs.cfm?fid=4640

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

Why not have have as a record: (Primary Key is Brand+Model)
Brand Model Style Type_Indicator
Toyota Camry coupe C (for car)
etc.

Seems to me you can then get a list of any brand and their models, any style, any type, and you can filter out whatever. eg. exclude T (for truck).

 

[r937]

if Brand+Model is the PK, then you cannot have more than one style of each

r937.com | rudy.ca

 

[fneily]

Gee, OK, then Brand+Model+Style. One should know how to create PK's.

 

[BobRodes]

First, I don't see why you need to have the style be "under" the model, since each is a function of a vehicle. For example, there can be all kinds of models that are coupes.

Also, you might not need both brand and model for each vehicle, since a model implies a brand. For example, there aren't any Ford Corollas. So, each vehicle has a style and a model and a type, each model has a make. I have a large auto dealer program that is based on this premise, and it works fine.

HTH

Bob

 

[Tamrak]

Thank you for all of your replies…

After discussing the issue with the managers, they gathered the data imported from other departments. It came in the form of Excel, which contains over 8,000 lines (records). There are six columns (fields).

* Record Number (Number field)
* Type (Text)
* Brand (Text)
* Model (Text)
* Style (Text_
* Doors (Text – such as 2dr, 3dr, 4dr)

I imported this information into MS Access table. I created a form, which would have combo boxes and list box.

The objective of the design is to create four combo boxes that will select type, brand, model and style from the table. The last field, Doors, has to be list box because it should allow single or multiple selections.

When I created a form – frm_Vehicles, I also put a subform, sfrm_Vehicles, below in order to view the entire table in datasheet format. This is to see what the selection has been made so far. If I choose, type = “car” in the first combo box, all cars will be displayed in the subform. When I continue to select brand, it will start narrowing down.

I was successfully created the codes, after update, on the first four fields. The codes are as follow:

-------------------------------------------------------------------------
Private Sub cboType_AfterUpdate() ‘ For vehicle Type

Dim strSQL As String
Dim strSQLSF As String

cboBrand = Null
cboModel = Null
cboStyle = Null
lstDoors = Null

strSQL = "SELECT DISTINCT tbl_Vehicles.Brand FROM tbl_Vehicles "
strSQL = strSQL & " WHERE tbl_Vehicles.Type = '" & cboType & "'"
strSQL = strSQL & " ORDER BY tbl_Vehicles.Brand;"

cboBrand.RowSource = strSQL

strSQLSF = "SELECT * FROM tbl_Vehicles "
strSQLSF = strSQLSF & " WHERE tbl_Vehicles.Type = '" & cboType & "'"

Me!sfrm_Vehicles.LinkChildFields = "Type"
Me!sfrm_Vehicles.LinkMasterFields = "Type"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
---------------------------------------------------------------------------
Private Sub cboBrand_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboModel = Null
cboStyle = Null
lstDoors = Null

strSQL = " SELECT DISTINCT tbl_Vehicles.Model FROM tbl_Vehicles "
strSQL = strSQL & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQL = strSQL & " tbl_Vehicles.Brand = '" & cboBrand & "'"
strSQL = strSQL & " ORDER BY tbl_Vehicles.Model;"

cboModel.RowSource = strSQL

strSQLSF = " SELECT * FROM tbl_Vehicles "
strSQLSF = strSQLSF & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Brand = '" & cboBrand & "'"


Me!sfrm_Vehicles.LinkChildFields = ""
Me!sfrm_Vehicles.LinkMasterFields = ""

Me!sfrm_Vehicles.LinkChildFields = "Type;Brand"
Me!sfrm_Vehicles.LinkMasterFields = "Type;Brand"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
-----------------------------------------------------------------------------------
Private Sub cboModel_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cboStyle = Null
lstDoors = Null

strSQL = " SELECT DISTINCT tbl_Vehicles.Style FROM tbl_Vehicles "
strSQL = strSQL & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQL = strSQL & " tbl_Vehicles.Brand = '" & cboBrand & "' And "
strSQL = strSQL & " tbl_Vehicles.Model = '" & cboModel & "'"
strSQL = strSQL & " ORDER BY tbl_Vehicles.Style;"

cboStyle.RowSource = strSQL

strSQLSF = " SELECT * FROM tbl_Vehicles "
strSQLSF = strSQLSF & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Brand = '" & cboBrand & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Model = '" & cboModel & "'"

Me!sfrm_Vehicles.LinkChildFields = ""
Me!sfrm_Vehicles.LinkMasterFields = ""

Me!sfrm_Vehicles.LinkChildFields = "Type;Brand;Model"
Me!sfrm_Vehicles.LinkMasterFields = "Type;Brand;Model"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
------------------------------------------------------
Private Sub cboStyle_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

lstDoors = Null

strSQL = " SELECT tbl_Vehicles.Doors FROM tbl_Vehicles "
strSQL = strSQL & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQL = strSQL & " tbl_Vehicles.Brand = '" & cboBrand & "' And "
strSQL = strSQL & " tbl_Vehicles.Model = '" & cboModel & "' And "
strSQL = strSQL & " tbl_Vehicles.Style = '" & cboStyle & "'"
strSQL = strSQL & " ORDER BY tbl_Vehicles.Doors;"

lstDoors.RowSource = strSQL

strSQLSF = " SELECT * FROM tbl_Vehicles "
strSQLSF = strSQLSF & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Brand = '" & cboBrand & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Model = '" & cboModel & "' And "
strSQLSF = strSQLSF & " tbl_Vehicles.Style = '" & cboStyle & "'"

Me!sfrm_Vehicles.LinkChildFields = ""
Me!sfrm_Vehicles.LinkMasterFields = ""

Me!sfrm_Vehicles.LinkChildFields = "Type;Brand;Model;Style"
Me!sfrm_Vehicles.LinkMasterFields = "Type;Brand;Model;Style"
Me.RecordSource = strSQLSF
Me.Requery

End Sub
----------------------------

Problems:

1. When I tried to construct the listbox (lstDoors). I got the list popped up right away. It supposed to be blanked.
2. My co-workers recommended me to create a query to pull the results. This means that the query has to override very time the program runs, in order to generate a result. We named it, qry_multiselect.
3. We tried to create a sub procedure. However, it did not work at all. I believe that I made a big mistake. My purpose is to have the information selected, displayed on the datasheet (in the subform).
4. I set the property of the list box as “Multi Select – Extended”
5. If an expert on the program can look and see why the sub procedures below do not work, please let me know.
6. Is there anyway to have the first line in the list box blanked? I put the default value as “Null” in the property and program, it still does not work. I even forced the sub procedures, on load to have lstDoors = Null. It still does not work.
7. Problem might also rely on the last sub procedures, cboStyle_AfterUpdate(). I am not sure. I think it might, because they are kind of related to each other.

-------------------------------

Anyway, the temporary codes for the list box are below -> (does not work).

Private Sub lstDoors_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_MultiSelect")

For Each varItem In Me!lstDoors.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstDoors.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = " SELECT * FROM tbl_Vehicles "
strSQL = strSQL & " WHERE tbl_Vehicles.Type = '" & cboType & "' And "
strSQL = strSQL & " tbl_Vehicles.Brand = '" & cboBrand & "' And "
strSQL = strSQL & " tbl_Vehicles.Model = '" & cboModel & "' And "
strSQL = strSQL & " tbl_Vehicles.Style = '" & cboStyle & "' And "
strSQL = strSQL & " tbl_Vehicles.Doors IN(" & strCriteria & ");"

Me!sfrm_Vehicles.LinkChildFields = ""
Me!sfrm_Vehicles.LinkMasterFields = ""

Me!sfrm_Vehicles.LinkChildFields = "Type;Brand;Model;Style;Breakdown"
Me!sfrm_Vehicles.LinkMasterFields = "Type;Brand;Model;Style;Breakdown"
Me.RecordSource = strSQL
Me.Requery

qdf.SQL = strSQL

DoCmd.OpenQuery "qry_MultiSelect"

Set db = Nothing
Set qdf = Nothing
End Sub
----------------------
Thank you for your time and point us to the right direction. ALl comments are welcome. Please do not instruct us to completely change the codes or restructure the tables. That's all we ask.