How to Reference Data from a different Table?

[Adigga1]

Hello everyone,

Here is the scanario:

Tables:

T_Physicians
fields: (pk)TaxID, PhysicianName

T_ServiceCodes (I have 3 of these tables)
fields: (pk)Servicecode, ServiceName, Price

Mission:

Associate each TaxID to each ServiceCode, mind you, each Physician have their own Service codes that they bill.

Should I build a Query with a Criteria to find it in 1 Table or Build Separate lookup ServiceCodes table to reference each TaxID in T_Physicians table?

Enclosed is the SQL code of my QUERY;

What i'm trying to accomplish is this: say for example the Physician TaxID is equal to Sibley Hospital, I would like a means to automatically reference the SibleyCPT table inorder to choose my Sibley service codes and prices each time the specified TaxID is selected.

SELECT T_Service.ServiceNumID, T_Service.TaxIDNum, T_Physicians.TaxID, T_Physicians.HospitalName, T_Service.SibleyNum, T_SibleyCpt.CPTcode, T_SibleyCpt.Charge, T_Service.ECCcptNum, T_ECCcpt.CPTcode, T_ECCcpt.Charge
FROM T_SibleyCpt INNER JOIN (T_Physicians INNER JOIN (T_ECCcpt INNER JOIN T_Service ON T_ECCcpt.ECCcptNum = T_Service.ECCcptNum) ON T_Physicians.TaxIDNum = T_Service.TaxIDNum) ON T_SibleyCpt.SibleyNum = T_Service.SibleyNum;

Please Help
thank you in advance
---adigga1

 

[jzwp22]

First, all of the service codes should be in 1 table not 3.

If the same service code is used by multiple physicians then you will need to set up a junction table

tblPhysicianCodes
pkPhysCodeID primary key, autonumber
fkTaxID foreign key-->T_Physicians
fkServiceCode foreign key-->T_ServiceCodes

If the the service codes are unique to a physician then you just need to modify your service code table by adding a foreign key relating the code to the physician.

T_ServiceCodes
(pk)Servicecode
ServiceName
Price
fkTaxID foreign key-->T_Physicians

 

[Adigga1]

First, all of the service codes should be in 1 table not 3.

If the same service code is used by multiple physicians then you will need to set up a junction table

tblPhysicianCodes
pkPhysCodeID primary key, autonumber
fkTaxID foreign key-->T_Physicians
fkServiceCode foreign key-->T_ServiceCodes

If the the service codes are unique to a physician then you just need to modify your service code table by adding a foreign key relating the code to the physician.

T_ServiceCodes
(pk)Servicecode
ServiceName
Price
fkTaxID foreign key-->T_Physicians

Thank you for the reply,

The challenge I am having with the Physician and Service code tables is that:
1. The Service Codes are unique to each physician and
2. The service code and price start from a range of one to maybe 50 different codes; while another physician may only use 5 codes.

So, I'm unsure as to the manipulation of this sort of data within 1 table.

Thank you in advance

 

[jzwp22]

I'm not sure what manipulations you are referring to. Can you go into a little more detail. Are you saying that the service codes have to be in sequential order for each physician? I would use a separate autonumber primary key for the table and then have the actual service code as another field. There are ways using the DMax() function to increment the value in the service code field and you can do it for each fkTaxID.

T_ServiceCodes
pkServCodeID primary key, autonumber
ServiceCode
ServiceName
currPrice
fkTaxID