If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design Questions
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. |
#2
|
|||
|
|||
Design Questions
(comments in-line)
"down in flames" wrote in message ... 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Design Questions
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" wrote: 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. |
#4
|
|||
|
|||
Design Questions
Awesome thank U!
"Clifford Bass" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
Design Questions
You are welcome!
Clifford Bass "down in flames" wrote: Awesome thank U! "Clifford Bass" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|