I'm trying to put together a relatively simple Service Rate database. We sell Services, which can be combined into Packages. The Package price can be the sum of the individual Service prices, or it could be some discount off that total. For my purposes, I can assume that the discount will be manifested by a reduction in the price of one or more Services for that Package/Customer combo. The same package could be sold to multiple customers, but they could have different discounts. So far, this is what I have:
tblCustomerMaster
CustID (PK)
CustName
tblServiceMaster
ServiceID (PK)
ServiceDesc
tblPackageMaster
PackageID (PK)
PackageDesc
tblPackageDetail
PackageID (PK)
ServiceID (PK)
CustID (PK)
Discount
tblRates
ServiceID (PK)
CustID (PK)
Rate
The problem I'm running into is that when I try to create a form (and sub-form), I get too many records in my sub-form. I'd like to have a form that shows the Customer and Package in the main section, and in the sub-form, display the Services in that Package (for that Customer), the rate and discount, and then the calculated "net rate". However, I'm running into two problems.
First, I get too many "records" in my main form. I should only have one record for each Customer/Package combo as defined by the tblPackageDetail table (this may be due to how I've created the form).
Second, my subform (which is tabular) is showing multiple records for each service (one for each different customer rate). This occurs even though I've linked the subform to the main form based on ClientID. Perhaps I need to include the ClientID field from the Rates table as well.