options in table

[Old bloke]

Ignore the first two paragraphs....(I just needed a whinge and my wife
doesn't care about such things!)

When I started with Access 2.0 this form and that form and normalization
didn't exist. I have been told that this is my answer but it all seems like
a complicated method of simply saying what I learned as "good design and
structure". (I feel sorry for the newbie's (normalization indeed! That's
barely a word.)

Since 2.0 through 97 and 2000 I have always stuck to what the access bibles
taught me which essentially was use as few tables as possible unless the
data is unrelated. In all the my builds this has always worked very well and
very quickly. But despite delving the depths of dde,api and dlls I now find
myself back at square one with a very simple design query.


The Issue>>>>

I have never had any requirement to utilize many-to-many relationships and
find that I must be missing the obvious as:

a. I cannot see the need for such complexity in this instance (regardless of
it being desirable,ideal etc.) and

b. Despite setting up all the tables and relationships as required I do not
get the results I need


I have a form based on a main table (detail not important)

I have a table that contains my products (only 12 I might add and this is
unlikely to go much higher)

Currently this also contains four other checkbox fields that indicate four
possible delivery options for any given product (allowing the end user to
change them from a simple form, individual products have a very varied
number selected)

All I want to do is to fill a combo with the available delivery options
after the user has selected the product in from previous combo. I can see
ways of doing this using code but despite creating the following

table: products
table: delivery options
table: combining table

and assigning all the relationships and keys as per a hundred examples I
fail to see how this can help me achieve the goal of returning several
records (the delivery options for display ion my combo) for any single
product.


Please help this old boy see the light! Any help much appreciated. Many
thanks.

 

[Jeff Boyce]

As one old timer to another ... <g>

First, why? Why even bother using Access when it sounds like you could use
a spreadsheet? What is it that you believe Access adds that you can't get
from, say, Excel?

Next, to get better use of the relational feature in Access, you need to
design tables accordingly. When you have 4 fields (or 40) with checkboxes,
for which any number (or none) may be checked, the only way to determine
which ARE checked is to write complex expressions or use code to cycle
through them all.

On the other hand, a simple table that holds:

trelShippingAvailability
ShippingAvailabilityID (?autonumber)
ProductID (foreign key, from tblProduct)
DeliveryOptionID (foreign key, from tlkpDeliveryOption)

gives you one row for each Delivery Option applicable to a given Product.
Now your combo box gets filled by a query that basically says "show me all
DeliveryOptions for this Product".

 

[Old bloke]

Jeff,

Many thanks for the rapid response

Why access? After reviewing my question I can see why you would ask this.
These tables are just a small part of a large database I did that I am
adding to.

Before your reponse I rehashed the tables yet again to produce a slightly
clearer version of what I already had. This gave me the tables as per your
example but I'm still not getting what I need (or rather my brain isn't!).

the table

trelShippingAvailability
ShippingAvailabilityID (?autonumber)
ProductID (foreign key, from tblProduct)
DeliveryOptionID (foreign key, from tlkpDeliveryOption)

So I setup a form to fill the table as follows:
ProductID < Set via combo
DeliveryOptionID < Set via combo

but I also need a field to store the textual description e.g DelOptionDesc
and of course have any changes to tlkpDeliveryOption [DeliveryOptionDesc]
cascade down.

How can I achieve this?

 

[Jeff Boyce]

No! (as in "don't go there!" <G>)

If you already have a table that holds the DeliveryOption rows, the
description field you mentioned belongs THERE, one time, one place.

When you use a form to look at the delivery options, you include the
description field from that table (but if you save a Product X
DeliveryOption pair/row, you only save the IDs).

In a well-designed relational database, you don't need to repeat information
(attributes) like name, address, description, ... every time you refer to
the ID of the row that holds that info -- that's what queries are for.

 

[Old bloke]

Jeff

I already knew all this **BUT IT'S CLEAR I HAD FORGOTTEN!**

Many thanks! My original question (different place) was actually what query
do I use? It was rather chicken and egg of course because without the
correct relationships I couldn't see the query and without that query I
couldn't get my head around how it would all come together.

The shot across the bows woke me up! Spending the other half of my life
telling companies what a mess their network structures and systems are and
how they should start by getting their structures (relationships) right I
thought that I should eat humble pie. (To much time with my head up the
backside of VBA and IP addresses!) I literally blew the dust off 'Msaccess
RELATIONAL DATABASE management system....' and read page 32 !!!!

Many thanks for your long suffering on this issue....(I did read other
related posts but just couldn't get it). Please keep up the good work.