I have a list of Twelve Insurance Companies
Each Insurance Company furnishes all the products, but with different
prices
from one
Insurance Company to another.

okay, having read your further explanation as well, we're actually talking
about services paid for by insurance companies.
so one service may be paid for by many companies, and one company may pay
for many services. also, one patient may receive many services (with payment
from one or many companies), and one service (from any company) may be
received by many patients. these are many-to-many relationships, which are
not correctly represented in your posted table design. suggest the
following, as

tblCategories
CatID (primary key)
CatName
CatDescription

tblServices
ServID (pk)
ServName
CatID (foreign key from tblCategories)

tblInsCompanies
CoID (pk)
CoName
CoPhone
CoFax
i'll go with that setup, though it's not normalized; proper design would
call for a communications table (linked to tblInsCompanies) where you can
enter multiple records for each company - phone, fax, email, website, etc.
or at the very least, a simple phone number table, linked as noted above.

tblCompanyServices
CoServID (pk)
CoID (fk from tblInsCompanies)
ServID (fk from tblServices)
UnitPrice
you don't need a category field in this table, because each service is
already linked to a specific category, in tblServices.
note that you can dispense with the CoServID primary key field, and use the
two foreign key fields as a combination pk. i prefer to not do that,
especially when the table's primary key will be used as a foreign key in
another table, as below.

tblPatients
PatID (pk)
PatFirstName
PatLastName
PatDOB (don't store a patient's *age*, because that keeps changing. store
the date of birth, and you can calculate the age whenever and wherever
needed.)
other fields that describe a patient only, no data about the services
received by a patient.

tblPatientServices
PatServID (pk)
ServiceDate
PatID (fk from tblPatients)
CoServID (fk from tblCompanyServices)
Cost
this situation calls for bending the normalization rule re duplicating
data. cost for each service should be stored in the service record, because
over time the price that each insurance company pays for a given service
will change - but the price paid for a specific service to a specific
patient in the past should not change, so it must be hard-coded in this
table.

but, much better than just going with the above design, suggest you read up
(or read more) on relational design principles before proceeding. correct
tables/relationships design is critical to successful database development,
so it really pays to invest the time up front to get it right. and, armed
with an understanding of relational design principles, you're the best
person to structure your database - because you know more about your
business process than you can hope to tell us in this forum. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

last but not least, this is an *extremely* simplified medical services
tracking/billing structure. if it truly meets your needs, that's fine; but
in a real-world medical practice, i have to think that one of the many
commercial products on the market would serve you better. you also need to
consider whether this, or any, setup you use is in compliance with the HIPAA
patient data security requirements (if you're in the USA, that is).

hth


"Silvex" u36021@uwe wrote in message news:758166b4acb83@uwe...
Hi people.
I am attempting to develop a new database and I'm new on Access.
It will work (I hope!) like this:
Database has 4 Tables:
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice)
tblInsurance (InsuranceID;CompanyName;Phone;Fax) and
tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice)

I have a list of Twelve Insurance Companies
Each Insurance Company furnishes all the products, but with different
prices
from one
Insurance Company to another.
I want to create a Form where I insert this data:
1)The Form will look like this: Frm Client based on TblClient
Date: ___(Field Date)_______
Name: ___(Field ClientName)_____________________
Age :__(fldAge) ___
And perhaps other fields to identify the client ..
Category: __cboCategory____(Rowsource from tblCategory)
Product: __cboProduct___ (filtered from the cboCategory)
Insurance Company: ____cboInsurance____(Rowsource from tblInsurance)
UnitPrice: __txtBox __(which should automatically fills with the Price for
that product that the Insurance company that I selected previously is
paying)
- AND where is where i'm stucked
2)In Date field I'll type the date of the record.
3)I type the Clients Names (the same client will have different records
for
different dates)
4)I type all the other fields that identify that particular Client
5)In the cboCategory I select the kind of work I did to that Client (Ex:
A-Consultation; B-Surgery; C-Exams ; D-Medications; .)
6)In the cboProduct appear the products filtered by cboCategory I
selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4). B-Surgery =
1)Laparotomy 2)Salpingectomy 3)Hernia repair 4).. and so on.
7)Then when I select the Insurance Company on the cboInsuranceCompany
Ex: AXA Insurance; MedicalInsurance; Doctorhelp;..)
the Unit Price txtBox will automatically fills with the price that the
Insurance company pays for that product, regardless of the client I
inserted.
Example1:
Date: 16-07-2007
Name: Silvex
Age:----
Category: Surgery
Product: Hernia repair
Supplier: AXA
Unit Price: fills automatically according with the price AXA pays for
Hernia
Repair
Example2:
Date: 25-06-2007
Name: Carol Stwart
Age:42
Category: Consultation
Product: Gyne
Supplier: Medical Insurance
Unit Price: fills automatically according with the price Medical Insurance
pays for Gyne Consultation
Example3:
Date: 5-07-2007
Name: Catherine Boulevard
Category: Surgery
Product: Hernia Repair
Supplier: Medical Insurrance
Unit Price: fills automatically according with the price Medical Insurance
pays for Hernia Repair

Sorry for my bad English, and thank you for any help in advance