Choosing correct rate 1

[Geekette]

In the database there are several companies. Currently there are 2 sets of rates (there may be more later on).<br>
How should I design this relation? I currently have a Company table [which includes a Long Integer field for RateTable#]; a RateTable table [companyID#, RateTable# as a Counter field]; and the 2 Ratetables [each has a TableID#].<br>
I run into problems when I try to form a query. How can I specify that Company A with RateTable#1 should go to RateTable#1 when it's time to generate an invoice?<br>
I had originally had just one RateTable, with different columns for each rate base. But I didn't know how to reference the required columns.<br>
Using DLookup is slow and inelegant for printing quantities of invoices.<br>
Any suggestions?

 

[elizabeth]

Can you give more information on the business problem? There are a lot of ways a Company could relate to a Rate<br>

 

[Geekette]

Thanks, Elizabeth, for your prompt reply.<br>
The business deals with 3rd party reimbursements from insurance companies. For example, for a given procedure, InsCoA may pay $100, while InsCoB pays $125. When initially entering an Insurance Company's info, I want to be able to specify which rate (or rate table) to use, so that 1)comparisons can be made vis a vis choice of doctors [if a doctor's rate is higher than the InsCo's reimbursement rate, that isn't good]<br>
2)invoices will correctly reflect the InsCo's rate structure [if an InsCo pays $100 for a procedure, that's what you have to bill]<br>
In an network database this is an easy thing to do; the relational model is very klugy.

 

[elizabeth]

You have a many-to-many relationship. Insert a third table to break this into 2 one-to-many relationships.<br>
Here's an example that uses tblCompany, tblProcedure and tblProcedureRate. <br>
In tblCompany you need only CompanyID and and descriptive fields associated.<br>
In tblProcedure you need only a procedureID, and any descriptive fields associated. <br>
In tblProcedureRate you need only ProcedureRateID (type=AutoNumber), CompanyID, Procedure ID, and Rate.<br>
The relationship of tblCompany to tblProcedure Rate is one to many. <br>
The relationship of tblProcedure to tblProcedureRate is one to many.<br>
Require referential integrity for both relationships.<br>
Set a unique index in tblProcedure rate for the combined Procedure & Company tables to ensure no dupes. <br>
In your query specify the ProcedureID and the Company ID and you will get the correct rate. <br>
<br>
Hope this helps <br>
<br>

 

[elizabeth]

Correction:<br>
&quot;Set a unique index... combined ProcedureID & CompanyID FIELDS...&quot;<br>
<br>

 

[Geekette]

Elizabeth, You are Indeed the Queen of Access!!!

 

[elizabeth]

Geekette, I still have a lot to learn about Access! ;-) My answer was based on general database design principles. But I appreciate your enthusiasm; be sure to post again if it doesn't work!!