A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Best way to design tables for cascading on my form



 
 
Thread Tools Display Modes
  #1  
Old January 8th, 2010, 06:51 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Best way to design tables for cascading on my form

My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields, etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box but
what is the easiest and best way to set up my tables & dictate which parts go
with which vehs?

Thanks for your help!

Pamela
  #2  
Old January 8th, 2010, 07:36 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Best way to design tables for cascading on my form

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which parts
go
with which vehs?

Thanks for your help!

Pamela



  #3  
Old January 8th, 2010, 07:42 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Best way to design tables for cascading on my form

had each vehicle type listed out w/ Yes/No boxes where I then selected
which parts go with which veh.
No check boxes. Consider this scheme --
Vehicles - field for VehicleType
Parts - one-to-many relationship VehicleType_Parts
VehicleType - one-to-many relationship VehicleType_Parts

Claim - one-to-many relationship Claim_Parts

Form (Claim) - select Vehicle - select VehicleType
Subform (Claim_Parts) - select Parts from VehicleType_Parts

--
Build a little, test a little.


"Pamela" wrote:

My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields, etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box but
what is the easiest and best way to set up my tables & dictate which parts go
with which vehs?

Thanks for your help!

Pamela

  #4  
Old January 8th, 2010, 07:42 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Best way to design tables for cascading on my form

Set the rowsource of the listbox to the query you created.

Steve


"Steve" wrote in message
...
First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in
the query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to
the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type
which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance
the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which
parts go
with which vehs?

Thanks for your help!

Pamela





  #5  
Old January 10th, 2010, 12:01 AM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Best way to design tables for cascading on my form

Thanks so much, Steve. I did exactly as you instructed but I don't
understand how/where the data is going to get connected. I have my VehTypes
in its table and I have the Parts List in its table. I recognize that we
established a junction table for the two but how does it get specified which
parts go with which VehType?? Do I need to create another form to try to do
that and, if so, what's the best method for this??

Thanks so much!

Pamela

"Steve" wrote:

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which parts
go
with which vehs?

Thanks for your help!

Pamela



.

  #6  
Old January 10th, 2010, 08:19 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Best way to design tables for cascading on my form

You record which parts go with which VehType in TblVehTypePart. For data
entry you need a form/subform. Base the main form on TblVehType. You need a
visible textbox on this form to record VehType and a not visible textbox to
hold VehTypeID. Base the subform on TblVehTypePart. Make the subform a
continuous form. You need a visible combobox and two not visible textboxes
on the subform. One textbox is for VehTypePartID and the other is for
VehTypeID. The combobox is for PartID. You need a query for the rowsource of
the combobox. Create a query based on TblPart and include PartID and Part.
Set sort for Part to ascending. Open the subform and set the rowsource of
the combobox to the query. Select the combobox and open Properties. On the
Data tab set Bound Column to 1. On the Format tab, set Column Count to 2 and
Column Width to 0;2. Now open the main form, select the subform control and
open Properties. On the Data tab, set the source object as the query and set
the LinkMaster and LinkChild properties to VehTypeID. You will now be able
to select a VehType on the main form and create a list of parts that go with
the VehType. Once you have done this for all the VehTypes, you will be able
to open the main form and automatically display all the parts that go any
VehType you select.

Steve



"Pamela" wrote in message
...
Thanks so much, Steve. I did exactly as you instructed but I don't
understand how/where the data is going to get connected. I have my
VehTypes
in its table and I have the Parts List in its table. I recognize that we
established a junction table for the two but how does it get specified
which
parts go with which VehType?? Do I need to create another form to try to
do
that and, if so, what's the best method for this??

Thanks so much!

Pamela

"Steve" wrote:

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in
the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on
my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars
and
I'd
like to make this list cascade to include only those parts relevant to
the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV,
etc.
which populates a cbo on my form where the user will select the type
which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go
with
which veh. This is where I'm really needing help. I know this isn't
the
best way to do it but can't figure out how else at this point to use
the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance
the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which
parts
go
with which vehs?

Thanks for your help!

Pamela



.



  #7  
Old January 11th, 2010, 02:17 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Best way to design tables for cascading on my form

For what follows, assume all ID fields that match the table names (e.g.
PartID in tblPart) are autonumber fields, and all other ID fields are Number
(Long Integer) fields.

As I understand, "parts" are general areas of the car, not specific parts.
The Parts table would contain a listing of all possible parts for all cars.
tblVehType is a listing of all vehicle types. One vehicle type has many
associated parts, and one part may be associated with many vehicle types,
which is why you need tblVehTypePart (a junction table). It is joined to
tblVehType and tblPart through the like-named fields (e.g. PartID joined to
PartID). To create the lists, create a form based on tblVehType (or better,
on a query based on the table), with a subform based on tblVehTypePart (or a
query). Bind a combo box to PartID on the subform. Use a query based on
tblPart as the Row Source for the combo box. Now you can create your list
for each vehicle type.

As was suggested elsewhere in this thread, you need a Claims record to enter
the actual information about what was damaged. The Claims table would have
ClaimID, Vehicle information, VehType (a number field), and other fields such
as ClaimDate and so forth. You will also need a ClaimDetails table. This
would be linked to tblClaim by way of ClaimID.

tblClaim
ClaimID
VehMake
VehModel
VehType
ClaimDate
etc.

tblClaimDetails
DetailID
ClaimID
PartID

Create a form based on tblClaim, with a subform based on tblClaimDetails.
The subform has a combo box bound to PartID. You will need code something
like this in the main form:

Function PartList()

Dim strSQL as String
Dim lngVehType as Long

lngVehType = Me.VehType

strSQL = "SELECT * FROM tblVehTypePart " & _
"WHERE [VehType] = " & lngVehType
Me.SubfromControlName.Form.ComboBoxName.RowSource = strSQL

End Function

Then in the AfterUpdate event of the combo box on the main form bound to
tblVehType, call the function.

Call PartList

Do the same in the form's Current event.

What you are asking is somewhat involved, although quite common. This is
just a summary sketch. Ask more questions as needed.


Pamela wrote:
Thanks so much, Steve. I did exactly as you instructed but I don't
understand how/where the data is going to get connected. I have my VehTypes
in its table and I have the Parts List in its table. I recognize that we
established a junction table for the two but how does it get specified which
parts go with which VehType?? Do I need to create another form to try to do
that and, if so, what's the best method for this??

Thanks so much!

Pamela

First you need tables like the following:
TblVehType

[quoted text clipped - 59 lines]

.


--
Message posted via http://www.accessmonster.com

  #8  
Old January 11th, 2010, 07:28 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Best way to design tables for cascading on my form

Thanks so much, Steve, for that suggestion and I followed your instructions.
I don't see, however, where/how the VehType & the Parts get matched. I
believe the 3rd table you had me create (I already had the first 2) is a
junction table for a many-to-many relationship (each veh has many parts, each
part has many vehs). Maybe I'm going down the wrong path here in my thinking
but what I've learned is that queries with more than 3 tables such as the one
you described cannot be updated so, again, how would I match VehType w/
Parts???
Thanks so much for helping me through this!!
Pamela

"Steve" wrote:

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which parts
go
with which vehs?

Thanks for your help!

Pamela



.

  #9  
Old January 11th, 2010, 07:35 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Best way to design tables for cascading on my form

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

"Steve" wrote:

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which parts
go
with which vehs?

Thanks for your help!

Pamela



.

  #10  
Old January 11th, 2010, 07:40 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Best way to design tables for cascading on my form

Thanks for that suggestion, Steve. I followed your instructions and created
the 3rd table, which as I understand is a junction table between VehType &
Parts. I don't see, however, how/where the VehTypes get matched with their
respective parts. As I understand, the query you had me build is not
updateable so how do I match them?? Thanks so much for your help with this!
Pamela

"Steve" wrote:

First you need tables like the following:
TblVehType
VehTypeID
Vehtype

TblPart
PartID
Part

TblVehTypePart
VehTypePartID
VehTypeID
PartID

Then create a query that includes the three above tables. The columns in the
query need to be:
VehTypePartID from TblVehTypePart
Part from TblPart
VehTypeID from TblVehType

Set Part to sort ascending. Set the criteria for VehTypeID to:
Forms!NameOfYourForm!cboVehType

Put the following code in the AfterUpdate event of cboVehType:
Me!NameOfTheListbox.Requery

Steve


"Pamela" wrote in message
...
My company inspects damaged cars for insurance purposes. Up to now,
(relating to this issue) I've had tblDamageArea populate a list box on my
form where the user can select the various parts of the car that were
damaged. I've had this list include parts for multiple kinds of cars and
I'd
like to make this list cascade to include only those parts relevant to the
car type inspected.

I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc.
which populates a cbo on my form where the user will select the type which
will then be used as the criteria for cascading the list box.

Before that, I created tblParts w/ the Parts but then also had each
vehicle
type listed out w/ Yes/No boxes where I then selected which parts go with
which veh. This is where I'm really needing help. I know this isn't the
best way to do it but can't figure out how else at this point to use the
tblVehType and connect the related parts to each veh.

Many of the parts are the same for all of the vehicles -- for instance the
front end parts - they all have bumpers, fenders, hoods, windshields,
etc.
but the doors (2 vs. 4), quarters (or beds), and rear ends all vary. I
imagine that I'll use a query to put them all together for the list box
but
what is the easiest and best way to set up my tables & dictate which parts
go
with which vehs?

Thanks for your help!

Pamela



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.