Figure out different combinations

[Tony]

Hello All:
I am new to access. I was wondering if you can help in designing a Access
data base that will figure out all of the combinations of different products.
Example:
Med 1 $17.80
Med 2 $19.23
Med 3 $25.63
Dental $1.50
Supp 1 $ 2.36
Supp 2 $3.63
Supp 3 $4.69

I see the data base having check boxes for the different products. Some
will check all but others may not. I would like the BD to figure out the
different combos like if they select Med 1 and Med 2 with Dental and Supp 1
and Supp 2 then I would like to see something like this:
Med 1 $17.80
Med 2 + Dental $19.30
Med 3 + Dental + Supp 1 + Supp 3 $27.78

I think you get what I am looking for, if not please let me know. You can
only select one medical at a time so you will never have Med 1 + Med 2, so
it would be great to have the other tow Medical option grey out after
selecting one. You can have any number of combos of Dental and Supp's

Thanks for your help on this and let me know if you have more questions......

 

[KARL DEWEY]

I think you get what I am looking for, if not please let me know.
No I do not. What is the business need for this database? How is the data
to be used?
Why do you think check boxes. It seems you are only thinking about display
and not about the table/field structure. You should have a table that list
your products and their varriations such as sizes, colors, price, etc. You
might want a customer list with their contact, shipping, and billing
information. Another table would be for the customers' orders with products,
quanities, order and delivery required dates, etc. Maybe another for billing
and payment recording.

 

[Tony]

We have different products with different prices. If a client only wants Med
1 with all three supp's then we need to know how much it will cost them as
well see all of the different combos the clients have selected for all of the
members. The data is beign used to determine the total monthly cost for our
products broken down to each person in a group of one to several thousand.
The important thing is you are only able to have one Med and and the other
will vary. But you do not have to have a Med1,2 or 3 at all. You can just
have dental alone or with any of the supp's

 

[KARL DEWEY]

Maybe I am just slow this morning but I still do not follow what is your
requirement.
It seems like you want to list all combinations with a single Med.

Is this some new treatment method or the new health care rationing plan?

 

[Tony]

No it is not some new plan, all of the health insurance providers have the
same general offerings. And yes just a list of all of the combos with a
single Med.

 

[Steve]

Hello Tony,

The first step in designing a database is to design the tables. Once you
have a correct design then you can design the forms and reports. Here is a
suggested start on the design of the tables:
TblPerson
PersonID
FirstName
MI
LastName
<address fields>

TblMed
MedID
Med (Med 1, Med 2, Med 3)
MedCost

TblProduct
ProductID
Product (Dental. Supp 1, Supp2, Supp3)
ProductCost

TblTreatment
TreatmentID
PersonID
MedID

TblTreatmentProduct
TreatmentProductID
TreatmentID
ProductID

You can use a form/subform to enter your data. Base the main form on
TblTreatment. Use a combobox to enter PersonID and use a combobox to enter
MedID. Base the subform on TblTreatmentProduct. Access will automatically
enter TreatmentID. Use a combobox to enter ProductID.

Steve
(e-mail address removed)

 

[Tony]

Thank You Thank You Thank You!!!
This is what I was looking for. With this information I SHOULD be able to
create my first data base. I just will have to you Google and help to figure
out how to do all of what you said. It seems pretty straight forward, but
then I have no idea about Access.....



Tony

 

[Steve]

Tony,

Note that you can avoid the complicarions of using code suggested here by
setting up your tables as I suggested. Putting Med in the main form rather
that combining it with Dental and Supp as suggested here limits a treatment
to one Med.

Steve
(e-mail address removed)