Design Questions

[down in flames]

I am starting what at first looked like a fairly simple project but I am
hitting some walls that I need some help with. What I am trying to do is
take several excel part number (by vehicle) listings and combining them into
one application in Access. The end users would enter vehicle make, model and
year via drop down boxes then would be shown which part numbers (across
several product lines) fit that specific vehicle.

I have started small by creating 3 tables:

tblVehicleInfo: Contains make, model, year info
tblHarness: Listing of part numbers for wiring kits
tblKits: Listing of part numbers for installation kits

I have entered data into all of the tables and they all use an autonumber
primary key. I am not sure if this is the best way to do it but it seemed to
make sense. The next step is where I have a few questions:

1) tblHarness and tblKits are product lines, how do I associate the part
numbers in these tables to specific vehicles? Almost every part number in
these tables would be listed for more than one vehicle.

2) When creating the drop down form to test the interface, how can I make it
"cascade" to only show entries applicable to the previous drop down. ie If
you select BMW in the "make" drop down box, only BMW models show up in the
"model" drop down box?

I have been using Excel for these types of projects for many years but I
think Access will provide a more space friendly approach if I can get a
handle on these types of basics. Any help would be appreciated.

 

[Dirk Goldgar]

(comments in-line)

I am starting what at first looked like a fairly simple project but I am
hitting some walls that I need some help with. What I am trying to do is
take several excel part number (by vehicle) listings and combining them
into
one application in Access. The end users would enter vehicle make, model
and
year via drop down boxes then would be shown which part numbers (across
several product lines) fit that specific vehicle.

I have started small by creating 3 tables:

tblVehicleInfo: Contains make, model, year info
tblHarness: Listing of part numbers for wiring kits
tblKits: Listing of part numbers for installation kits

It seems to me that you might do better to have a single table, tblParts, in
place of your tblHarness and tblKits. Each record in tblParts would have a
PartType field that would indicate whether it was a "Harness" part or a
"Kit" part (or a "Wiring" part vs. an "Installation" part). Having one
table would allow lookups to search one table rather than two. And if you
need to have separate lookups for Harness parts and Kit parts, the queries
could just have a where-condition that filters on PartType.

You may also want separate tables for Makes and Models, but you can probably
get along without them.

I have entered data into all of the tables and they all use an autonumber
primary key. I am not sure if this is the best way to do it but it seemed
to
make sense. The next step is where I have a few questions:

1) tblHarness and tblKits are product lines, how do I associate the part
numbers in these tables to specific vehicles? Almost every part number in
these tables would be listed for more than one vehicle.

You need an additional table, often called a junction table, to represent
the many-to-many relationship between parts and vehicles. This table would
have a compound primary key consisting of VehicleID and PartID, where
VehicleID is the primary key of tblVehicleInfo and PartID is the primary key
of tblParts. Let this table be called tblVehiclesParts. In
tblVehiclesParts, the individual key fields are Long Integer fields, rather
than autonumbers, and give this table a many-to-one relationship with
tblVehicles (linked by VehicleID), and a similar relationship with
tblParts(linked by PartID). The presence of a record in this table for
given VehicleID/PartID combination indicates that the specified part fits
that particular vehicle.

2) When creating the drop down form to test the interface, how can I make
it
"cascade" to only show entries applicable to the previous drop down. ie If
you select BMW in the "make" drop down box, only BMW models show up in the
"model" drop down box?

Each combo box's RowSource query would reference the value in the previous
combo box as a criterion for selection, and you'd have code in the
AfterUpdate event of each combo box to requery the combos that are later in
sequence. For example, if you had this rowsource for combo "cboMake":

SELECT DISTINCT Make FROM tblVehicleInfo;

Then you might have this for the rowsource of combo "cboModel":

SELECT DISTINCT Model FROM tblVehicleInfo
WHERE Make = [Forms]![frmVehicleInfo]![cboMake]
OR [Forms]![frmVehicleInfo]![cboMake] Is Null;

And you might have this for the rowsource of combo "cboYear":

SELECT DISTINCT ModelYear FROM tblVehicleInfo
WHERE
(Make = [Forms]![frmVehicleInfo]![cboMake]
OR [Forms]![frmVehicleInfo]![cboMake] Is Null)
AND
(Model = [Forms]![frmVehicleInfo]![cboModel]
OR [Forms]![frmVehicleInfo]![cboModel] Is Null);

Then you would have code to requery these combo boxes in the AfterUpdate
events:

Private Sub cboMake_AfterUpdate()

Me.cboModel.Requery
Me.cboYear.Requery

End Sub

Private Sub cboModel_AfterUpdate()

Me.cboYear.Requery

End Sub

That's the general idea.

 

[Clifford Bass]

Hi,

For question 1, you would create a couple of join tables.

tblVehicleHarnesses
VehicleID
HarnessID
Other pertinent information related to this combination

tblVehicleKits
VehicleID
KitID
Etc.

For question 2, you can use each combo boxes's Row Source properties
and On Change events to populate the succeeding combo boxes.

So the row source for the model combo box on a form named "MyForm" that
also has a make combo box named "cbMake" might be something like:

SELECT Model FROM tblModels WHERE Make = [Forms]![MyForm]![cbMake] ORDER BY
Model;

And the make combo boxes on change event might look like this:

Private Sub cbMake_Change()

cbModel.Requery
' Select the first item in the repopulated list
cbModel.Value = cbModel.Column(0, 0)
' You probably would want to clear out any combo boxes that are
dependent on the model such as trim line, year, engine size, etc.

End Sub

That should get you going in the needed direction.

Clifford Bass

 

[down in flames]

Awesome thank U!

Hi,

For question 1, you would create a couple of join tables.

tblVehicleHarnesses
VehicleID
HarnessID
Other pertinent information related to this combination

tblVehicleKits
VehicleID
KitID
Etc.

For question 2, you can use each combo boxes's Row Source properties
and On Change events to populate the succeeding combo boxes.

So the row source for the model combo box on a form named "MyForm" that
also has a make combo box named "cbMake" might be something like:

SELECT Model FROM tblModels WHERE Make = [Forms]![MyForm]![cbMake] ORDER BY
Model;

And the make combo boxes on change event might look like this:

Private Sub cbMake_Change()

cbModel.Requery
' Select the first item in the repopulated list
cbModel.Value = cbModel.Column(0, 0)
' You probably would want to clear out any combo boxes that are
dependent on the model such as trim line, year, engine size, etc.

End Sub

That should get you going in the needed direction.

Clifford Bass

I am starting what at first looked like a fairly simple project but I am
hitting some walls that I need some help with. What I am trying to do is
take several excel part number (by vehicle) listings and combining them into
one application in Access. The end users would enter vehicle make, model and
year via drop down boxes then would be shown which part numbers (across
several product lines) fit that specific vehicle.

I have started small by creating 3 tables:

tblVehicleInfo: Contains make, model, year info
tblHarness: Listing of part numbers for wiring kits
tblKits: Listing of part numbers for installation kits

I have entered data into all of the tables and they all use an autonumber
primary key. I am not sure if this is the best way to do it but it seemed to
make sense. The next step is where I have a few questions:

1) tblHarness and tblKits are product lines, how do I associate the part
numbers in these tables to specific vehicles? Almost every part number in
these tables would be listed for more than one vehicle.

2) When creating the drop down form to test the interface, how can I make it
"cascade" to only show entries applicable to the previous drop down. ie If
you select BMW in the "make" drop down box, only BMW models show up in the
"model" drop down box?

I have been using Excel for these types of projects for many years but I
think Access will provide a more space friendly approach if I can get a
handle on these types of basics. Any help would be appreciated.

 

[Clifford Bass]

You are welcome!

Clifford Bass

Awesome thank U!

Hi,

For question 1, you would create a couple of join tables.

tblVehicleHarnesses
VehicleID
HarnessID
Other pertinent information related to this combination

tblVehicleKits
VehicleID
KitID
Etc.

For question 2, you can use each combo boxes's Row Source properties
and On Change events to populate the succeeding combo boxes.

So the row source for the model combo box on a form named "MyForm" that
also has a make combo box named "cbMake" might be something like:

SELECT Model FROM tblModels WHERE Make = [Forms]![MyForm]![cbMake] ORDER BY
Model;

And the make combo boxes on change event might look like this:

Private Sub cbMake_Change()

cbModel.Requery
' Select the first item in the repopulated list
cbModel.Value = cbModel.Column(0, 0)
' You probably would want to clear out any combo boxes that are
dependent on the model such as trim line, year, engine size, etc.

End Sub

That should get you going in the needed direction.

Clifford Bass

I am starting what at first looked like a fairly simple project but I am
hitting some walls that I need some help with. What I am trying to do is
take several excel part number (by vehicle) listings and combining them into
one application in Access. The end users would enter vehicle make, model and
year via drop down boxes then would be shown which part numbers (across
several product lines) fit that specific vehicle.

I have started small by creating 3 tables:

tblVehicleInfo: Contains make, model, year info
tblHarness: Listing of part numbers for wiring kits
tblKits: Listing of part numbers for installation kits

I have entered data into all of the tables and they all use an autonumber
primary key. I am not sure if this is the best way to do it but it seemed to
make sense. The next step is where I have a few questions:

1) tblHarness and tblKits are product lines, how do I associate the part
numbers in these tables to specific vehicles? Almost every part number in
these tables would be listed for more than one vehicle.

2) When creating the drop down form to test the interface, how can I make it
"cascade" to only show entries applicable to the previous drop down. ie If
you select BMW in the "make" drop down box, only BMW models show up in the
"model" drop down box?

I have been using Excel for these types of projects for many years but I
think Access will provide a more space friendly approach if I can get a
handle on these types of basics. Any help would be appreciated.