my brain not comprehending the yes/no field

L

lela2a

I have a db that enters orders. The "product" (insurance plan) has one price
but 20 different "attributes" in any combo of one choice to many (plan types
like medical, dental etc...) and with one "price" (premium). The plan types
must show up on one line in a report with one price (they are not deliminated
per plan type). The easiest way for personnel to enter all the plan types
would be yes/no check boxes on the input form. My problem is I can't figure
out how to make those "yes"es equal the plan name in the report!
I know, it's probably very simple, but it is eluding me. If it involves
placing code somewhere, please assume I'm very ignorant and tell me precisely
where, ok?!
 
R

Roger Carlson

This may be the easiest way for your personnel to input information, but 20
different Yes/No fields is the worst way to design your database. What
happens when plan types change? You'll have to modify your tables, queries,
forms and reports.

What you should be doing is storing this information in a separate table.
In fact, you need 3 tables. A "Product" table, a "PlanType" table, and then
a linking table.

I believe a sample is worth a thousand words, so on my website
(www.rogersaccesslibrary.com), are two small Access database samples called
"ReallyBadDatabase.mdb" and "ReallyBadDatabaseReborn.mdb". The first shows
some of the problems associated with multiple Yes/No fields. The second
shows how the database should have been designed.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
L

lela2a

Hiya Roger, truly not seeing much difference between the multiple Yes/No
fields on your db and what I was considering - is it the quantity? ...and
totally not understanding your question about the plans changing. Do you
mean additional plans?

I think I must not have explained this very well. Each order (not plan) has
multiple plan types within it - up to 20 of different combinations. Think of
it more like ordering a quilt and you can have any of 20 differently designed
squares on it. Each square is always the same name, regardless whether the
design of each square can change internally. This db is not for all of that
detail, it is just for the choice of which squares to put on this quilt.

In the future, yes, new plans (such as the recent Flex Spending Acct plans)
could be added to the options, but not a significant number. There will
always be Medical, Dental, Life etc...

In any event, I did initially have the order table, the planName table and
the table to assign planNames to the orders, but I can't think of a simple
way for my Excel spreadsheet co-workers to enter this data. I think they
would definitely balk at multiple entries for the same order. SOOOO, I
thought check-boxes would be perfect. Except I have no idea how to then use
that data in the report. If you have an easy data entry idea - great! If
not, could you please tell me how to assign a textString to the Yes/No or
some such thing to make this work for me?
Thanks lots!
 
R

rpw

Hiya lela2a!

I looked at Roger's db and saw the difference but there is probably too much
going on with it to make the point he was trying to make (and it is a very
valid point). So, if you don't mind, I'll try an analogy:

Imagine your form as a printed Burger order form. On it you put the
customer's name and then check off each item that the customer wants added to
the burger. That's perfectly fine until the day that you need to add a new
item to it. Now, you have to redesign the whole printed form just to add the
one item, right? In db design that's the equivilent of what you're doing.

On the other hand, in Roger's methodology, your Burger order form has a
space for the customer's name and then below it you can add 1, 20, or any
number of items below the name by using stickers. And if you add a new item
to the list available, you don't have to redesign the printed form, you only
add a new sticker to the list.

In a properly designed Access form, the customer/name/product is the main
form and the selection of burger add-ons/plans would be in a sub-form with
drop-down combo boxes. Within each combo box is a list of all the items that
are available. As soon as you select/fill one then another blank is added on
the row below for you to add another if you choose. There is no limit to how
many you can have.

With this in mind, review Roger's "reborn" db again and look specifically at
how the sub-forms on the left hand side can be used as described above.

Hope this helps...
 
J

Jochen

There are a few things to consider here

you have a many-to-many relationship between your products and its
attributes because one product can hold multiple attributes and one attribute
can be linked to multiple products.
Several solutions would be

1) if you have multiple attributes for one product, add only one per row,
this means if you have 5 attributes for one product, you will have 5 rows for
the same product. every field would be repeated and only attribute would be
changed. You can see why this is not a very good solution, it's an obvious
case of redundancy.

2) You can accomodate multiple attributes with multiple columns, so if you
have 20 attributes, you could create 20 columns, but this will result in a
lot of empty fields and is not a very good solution

3) you can include every attribute in a single field, but this creates
problems as well, because it would be impossible to create an alphabetical
list of one of the attributes or how would you be able to search the database
in the future?

As you can see in the examples provided by Roger, the best way is to split
up this many-to-many relationship into two one-to-many relationships

This means, creating a new table which holds only your productID and your
attributeID (you could call it Product/Attribute), so you would end up with
three tables like rpw said: your products table, your attributes table and
your linked table (Product/Attribute)

so if you have a product ( let's say ProductID = 58 ) and it would have 5
attributes you would need to link to this product ( let's say AttributeID
12,15,21,35 and 36) your table would look like

ProductID AttributeID
58 12
58 15
58 21
58 35
58 36

This table would then go inbetween your other two tables and link it with a
one-to-many relationship to both tables

you can then easily create a similar form like the reborn database that
Roger provided

but what i suggest you first do is to get a good book about database design
and normalisation of data. I personaly use Access Database Design &
Programming published by O'Reilly. Having a good designed database is step 1
(and the most important step) for creating a good program.
 
L

lela2a

Thanks for the advice. As I said, I did have three tables designed for
normalization, but the data entry is a pain.

Roger's drop downs (I'm looking at the Induction Indications) would be
helpful if, when you chose one of them, it did not appear in the next drop
down so that the user doesn't get bombarded with errors if they lose track of
what they've already entered. The data they enter from is not in any sort of
order, and can have very similar names. Can no one think of a way for the
form's data entry to be like a check box but the design of the tables are not?
 
R

Roger Carlson

I suppose you could create an unbound form that would write those values to
a properly normalized table, but another possiblity exists. I have another
sample "ListBoxSubform.mdb" which shows how to use a multi-select listbox to
choose which values you want. Double-clicking on a value in the list brings
up a pop-up dialog box with all the values to choose from. What gets
displayed on the main subform is just the selected values. Very clean for
the user, IMO.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Top