lookup price of some services different by the Company Name

[Falcon88]

hiii dears

i have a db to collect the services of polyclinic center that handle with insurance companies and thier costumers.
- tblBillHead : contains a field companyID (fk connected to tblCompanyData on Pk CompanyID)
- tblBillDetails (conjunction table) : contains ServiceNo , BillNo , ServicePriceB
- tblServices : ServiceID , ServiceName , ServicePrice .

the services name is the same for all that companies , but the difference is on the Service Price , for every company a service price Example :

ServiceID	CompanyID2	ServiceName	ServicePrice
1	1	CBC	200
2	2	CBC	150
3	3	CBC	100
4	1	ESR	50
5	2	ESR	100
6	3	ESR	75

i do not want to reapeat the Service Name for every Company .because the names is the same for all that companies

what is the right method to deal with that problem ?

 

[plog]

Where are we? In a query, in a table? You wrote words about 3 tables and then threw a bunch of data at us that didn't conform to any of the prior listed tables.

I think the question is--I need a table that matches what companies get what prices for what services. Correct? If so, this is the table you need:

tblCompanyServicePrices
csp_ID, autonumber, primary key
ID_Service, number, foreign key to tblServices
ID_Company, number, foreign key to tblCompanyData
csp_Price, number, the price of the service for that company

That's it, those 4 fields will sort out what company gets what prices for what service.

My recommendation, is to complete the relationship tool in Access, expand the tables to show all fields, take a screenshot and post it back here and we can help make sure you have your table sructure correct.

 

[arnelgp]

i agree with the suggestion.
you need "another" table, and you use this in all your transactions.

 

[Gasman]

hiii dears

i have a db to collect the services of polyclinic center that handle with insurance companies and thier costumers.
- tblBillHead : contains a field companyID (fk connected to tblCompanyData on Pk CompanyID)
- tblBillDetails (conjunction table) : contains ServiceNo , BillNo , ServicePriceB
- tblServices : ServiceID , ServiceName , ServicePrice .

the services name is the same for all that companies , but the difference is on the Service Price , for every company a service price Example :

ServiceID	CompanyID2	ServiceName	ServicePrice
1	1	CBC	200
2	2	CBC	150
3	3	CBC	100
4	1	ESR	50
5	2	ESR	100
6	3	ESR	75

i do not want to reapeat the Service Name for every Company .because the names is the same for all that companies

what is the right method to deal with that problem ?

You have no choice, if you want to relate company,service and price?
Be it service name or service ID.

 

[Pat Hartman]

You need a table that defines ServiceName. That way you pick from a combo. The ID gets stored NOT the service name so you don't have to worry about changes to service names or a lot of typing. As long as the RowSource of the combo is sorted by ServiceName, type ahead will rapidly zero in on the value you want. Then the user presses tab to accept the current value. Where Gasman's example shows the ServiceName, it should actually be ServiceID but on your form, you will see the ServiceName. My suggestion is more like arnelgp's suggestion which correctly normalizes the data by using two tables. The only diffence is that the tblServicePrice needs a StartDT and an ExpirationDT for the price so you can enter the new price as soon as you know it. Say the new price for Acme starts on Sept 9. Change the ExpirationDT of Sept 8 in the Acme record and then add a new record with new price and a StartDT of Sept 9 and an ExpirationDT of 12/31/2050. I prefer a fixed expiration date rather than null because it makes it much easier to zero in on the correct record.

In the record where the service is rendered, you chould copy the Price from the price record that is valid for that date. This simplifies a lot of future reporting and doesn't violate normal forms since price is a function of date. However if you add the dates to tblServicePrice, you don't need to copy the price, you can always obtain it with a join that uses the Start and Expiration dates to pick the price for any particular service rendered data. The price in this service rendered table is NEVER updated. And once the record is saved, it should not allow any updates at all anyway. The only possible update is to cancel the service or void it.

And the complications continue If your service record is a date range. Ie Start on Sept 8 and end on Oct 5, then the problem becomes, what happens if there is a price change in that window. Does the client get the price on the start date for the entire term or do you have to break any active service record whenever it is affected by a price change. So the service record now becomes Sept 8 to Sept 29 and you have to create a new one for Sept 30 to Oct 5.

Nothing is easy when it comes to billing for services. Product billing is a lot easier but then you have inventory to worry about so maybe it's a break even