autofill

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains client insurance information (clientname,
clientins). I have another table that contains insurance rates for three
services - there will never be more than these three services although there
may be new insurance companies (inscompany, servicerate1, servicerate2,
servicerate3). I then want to create records of services with a date, a
clients name, and type of service in another table/form and then populate a
fourth field with the insurance companies rate for the particular service.
The rate will be dependent on the type of insurance company and the type of
service. Please help.
 
Consider redesigning your Services table. Having 3 fields servicerate1,
servicerate2, servicerate3 is a sure sign that the table hasn't been
properly normalized: that's called a repeating group.

Even if every company always has 3 services, and you're positive there will
never be more than 3 services, you still should have the service rates as 3
separate rows in a second table, rather than 3 separate columns in the same
row.

By having a table with

inscompany
servicetype
servicerate

it should be straightforward to do what you're trying to do.

Jeff Conrad has a number of good resources regarding database normalization
at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
 
I will redesign. Can you help me with the next step? How do I get the
form/table to autofill the service rate?

Many thanks.
 
Well, you definitely don't want to put the rate in a different table: having
the data redundantly in two places is a bad idea.

Create a query that joins the tables together. Since you know the insurance
company and the service, that will allow you to pick the appropriate rate
from the new table you're going to be building.
 
Back
Top