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
0
Utf
1/8/2010 5:51:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

12 Replies
1036 Views

Similar Articles

[PageSpeed] 55

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
santus@penn.com

"Pamela" <Pamela@discussions.microsoft.com> wrote in message 
news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
> 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 


0
Steve
1/8/2010 6:36:43 PM
>>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
0
Utf
1/8/2010 6:42:02 PM
Set the rowsource of the listbox to the query you created.

Steve


"Steve" <notmyemail@address.com> wrote in message 
news:%23N6tHGJkKHA.1864@TK2MSFTNGP05.phx.gbl...
> 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
> santus@penn.com
>
> "Pamela" <Pamela@discussions.microsoft.com> wrote in message 
> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
>> 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
>
> 


0
Steve
1/8/2010 6:42:14 PM
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
> santus@penn.com
> 
> "Pamela" <Pamela@discussions.microsoft.com> wrote in message 
> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
1/9/2010 11:01:02 PM
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
santus@penn.com


"Pamela" <Pamela@discussions.microsoft.com> wrote in message 
news:C8E35D38-A18A-4328-BBDC-AE3A5DDA1FAD@microsoft.com...
> 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
>> santus@penn.com
>>
>> "Pamela" <Pamela@discussions.microsoft.com> wrote in message
>> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
>> > 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
>>
>>
>> .
>> 


0
Steve
1/10/2010 7:19:43 PM
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

0
BruceM
1/11/2010 1:17:25 PM
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
> santus@penn.com
> 
> "Pamela" <Pamela@discussions.microsoft.com> wrote in message 
> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
1/11/2010 6:28:02 PM
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
> santus@penn.com
> 
> "Pamela" <Pamela@discussions.microsoft.com> wrote in message 
> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
1/11/2010 6:35:01 PM
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
> santus@penn.com
> 
> "Pamela" <Pamela@discussions.microsoft.com> wrote in message 
> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
> > 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 
> 
> 
> .
> 
0
Utf
1/11/2010 6:40:02 PM
Hi Pamela,

Forget the query in my first reply, forget my second post and forget your 
form and listbox. Follow my suggestion in my third post.

Steve


"Pamela" <Pamela@discussions.microsoft.com> wrote in message 
news:C21F2706-90C0-405A-883A-1768A20ECCD2@microsoft.com...
> 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
>> santus@penn.com
>>
>> "Pamela" <Pamela@discussions.microsoft.com> wrote in message
>> news:88E9C415-EA53-4895-AD71-B57F5847E683@microsoft.com...
>> > 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
>>
>>
>> .
>> 


0
Steve
1/11/2010 7:54:50 PM
You are correct about the junction table.  Note that what Steve suggested (I
described something similar) is only a way of building a list of parts that
are associated with vehicle types (although I don't see that you need hidden
text boxes).  My previous posting described how to use the resulting data to
limit the listing of available parts on the Claims form.

To sum up, you need a list of parts associated with a Vehicle Type

THEN

you need to use that list to provide the Row Source for a combo box on the
Claim Details subform on the Claims form.

It should be possible to add an extra field to tblPart to indicate whether
the part is on all vehicles.  Your combo box list could incorporate the
custom list for the vehicle type, plus the default parts that are associated
with all vehicles.

Pamela wrote:
>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
>
>> First you need tables like the following:
>> TblVehType
>[quoted text clipped - 59 lines]
>> 
>> .

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

0
BruceM
1/11/2010 8:26:45 PM
Thanks so much, Bruce.  I'm sorry that this appears to be a very late reply, 
but for whatever reason, my system (or perhaps it was the website)  wasn't 
correctly displaying the responses here for a few days.  

This VehType/Part situation is really just what someone else has called 
ancillary data - going to be used only one time per file which is why the 
idea of the Parts being displayed in a Multi-Select list box which doesn't 
save the users choices after leaving that record really isn't a problem.  

I want to get this portion up and running but then I also want to grow this 
db to emcompass much more of the work we have to do so I'm always wanting to 
make sure I'm not hindering myself for that future growth.  With that in 
mind, you suggested to make a tblClaim (which I have) but in it, you listed 
the vehicle Make & Model.  All of my designs have had a separate tblVehicle 
because it seemed that having the Vehicle info in w/ the Claim info broke 
normalization rules.  Now if that was just a fast example to try to 
illustrate to me how to connect all of this VehType/Parts to the actual 
claim, I can understand that but if there is another reason you would put 
those together,  I'd be very interested in the thought behind it.

Thanks so much!

Pamela



"BruceM via AccessMonster.com" wrote:

> You are correct about the junction table.  Note that what Steve suggested (I
> described something similar) is only a way of building a list of parts that
> are associated with vehicle types (although I don't see that you need hidden
> text boxes).  My previous posting described how to use the resulting data to
> limit the listing of available parts on the Claims form.
> 
> To sum up, you need a list of parts associated with a Vehicle Type
> 
> THEN
> 
> you need to use that list to provide the Row Source for a combo box on the
> Claim Details subform on the Claims form.
> 
> It should be possible to add an extra field to tblPart to indicate whether
> the part is on all vehicles.  Your combo box list could incorporate the
> custom list for the vehicle type, plus the default parts that are associated
> with all vehicles.
> 
> Pamela wrote:
> >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
> >
> >> First you need tables like the following:
> >> TblVehType
> >[quoted text clipped - 59 lines]
> >> 
> >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
1/13/2010 3:06:01 PM
Reply:

Similar Artilces:

Additional lookup for contacts on opportunity form
Hi, I try to customise CRM 3.0 without any training - therefore a question for which I need some help. On the opportunity form exists a look-up (potentional customer) for contacts/accounts. I'd like to add an aditional one to the form (to replace the decision maker). I tryed to create a new field on the opportunity entity but there's no field-type "look-up". It's also not possible to create a new relationship to the contact entity (both system entity's). Does anybody of you know how I can do this? (if yes - please explain it more detailed...). Thanks Joerg Look...

Forms 07-27-07
Hi, A VB Code is used that has the following line: Me.Visible = False (Me refers to the forms names frmBirthsearch) After this line a second form (form2) is opned based on a query that reads two dates from the frmBirthsearch using the line code: DoCmd.OpenForm "frmBirthdates", acViewNormal at this point frmbearthsearh is hidden. What command shold I add after this in order to close the frmbirthsearch? ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Beginner
Hi ! I am a beginner to databeses, and particulary to MS Access 2003. (althought I passed the first lessons) I want to make a small database with possibility to grow up. (max. 100 entries for a table) I think that in my case the data must be structured like loop into loop. Like that: Year: 2000 2001 2002 2003 etc. Plant: plant_1 plant_2 plant_3 etc. Generator: gen_1 gen_2 gen_3 etc. So for every Year it must exist some Power Plants, and for every Power Plant it must exist some Generators. I already have ma...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

GL figures
what really is the best practice method for brining in monthly GL figures so users can get comparative statements this year/lastyr. do you bring in all the old detail journals? or do you load via a maintenance or IM the historical balance for each month in some file. -- Linda W. Linda, You mean when you are starting up a new company? If so, unless someone needs the detail imported into GP for other reasons, I bring in a beginning balance and monthly net changes only. For example, let's say you're starting to use GP with January 2009, your fiscal year ends in December and y...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Enable/Disable a Form Control Based on Security Group Permissions
How do I enable or disable a control in a form based on a user’s security group membership? For example: If I have a checkbox on a form (call it box1), I want box1 to be enabled if the user who opened Access is a member of a security group called “Breaker Test Admin.” For members of any other group (except of course “Admins”), box1 should be disabled. Thank you, for your help! On Mon, 02 Jul 2007 18:57:13 GMT, "BenS" <u35527@uwe> wrote: >How do I enable or disable a control in a form based on a user�s security >group membership? For example: If I have a checkbox on a ...

Trying to Create a pop-up form
Due to size limitations on my main form, I am trying to create a form that, when I select a button, it pops up like a dialog box with the main form, Frm_Escheat, in the background I've created a qry which is the record source on my pop-up form. In the criteria of the query is: [Forms]![Frm_Escheat]![statecode] where [statecode] is the field from the Master Form. So, my "pop-up" form is activated when I select the button, but my problem is that it just goes to the pop-up form without the main form showing in the background. Thanks for your help. It would probably hel...

Auto Fill a Textbox in Form
What is the simplest way to auto fill a textbox (in a form) with the maximum values of more than one field in the last record entered, in the same table? Can the =DMax function be used for this? I have tried =DMax(“[field_1]”, “[field_2]”, “[field_3]” , “table_1”)+1, but this doesn’t work. Is something like this possible with two or more feilds with DMax? Thanks -- Message posted via http://www.accessmonster.com See MinOfList() and MaxOfList() here: http://allenbrowne.com/func-09.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allen...

user forms #5
Hello, I have created a user form (click on New Employee). How do I get the information entered in to this form to appear in the correct columns in the Payroll data sheet? And how do I get the combo boxes to display the drop down information from the sheet called input. Regards Runner77 -- Runner77 ------------------------------------------------------------------------ Runner77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30388 View this thread: http://www.excelforum.com/showthread.php?threadid=500502 ...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...