choosing form solution

[catalin.petrut]

I have a situation and i ask for a suggestion.
tbl_doctor with a list of doctors, tbl_jobs with a list of jobs that has a specific price for each one, and a tbl_special_price. In that last table i will have a list of special prices for some of the doctors for some of the jobs. So, some doctors will have a special price for some of the jobs. I also have a form where i insert doctor's info and a subform where i will insert the special prices.
My question for the suggestion is: in the subform is better to have the list of all jobs with the standard price and a field for the special price or should i insert each special price like a new row?

 

[Pat Hartman]

The subform should show only the jobs with the special price for that doctor.

Later on, the query to get the price for a job will need to join to both the standard table and the special table. The join to the special table must be a left join since not all jobs will have special prices. The query will create a "calculated" price field by examining both price fields and choosing one.

Select ..., IIf(IsNull(tblSpecial.Price), tblStandard.Price, tblSpecialPrice) As JobPrice, ...
From ...

 

[catalin.petrut]

I've made the query and the form has a NZ & DLOOKUP in after update. My problem is that i wonder if is best to have all the prices and a field to enter special price only on some jobs, or should i enter special prices one by one.
I have choose to enter them one by one. Is more simple.