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.