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
  #11  
Old January 11th, 2010, 08:54 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

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" wrote in message
...
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



.



  #12  
Old January 11th, 2010, 09:26 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

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

  #13  
Old January 13th, 2010, 04:06 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, 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

.

 




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 04:56 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.