How to Reference Data in a different Table?

[adigga1]

Here is the scanario:

Tables:

T_Physicians
fields: (pk)TaxID, PhysicianName

T_ServiceCodes
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?


Help:

How do I accomplish this?

[aas4mis]

Here is the scanario:

Tables:

T_Physicians
fields: (pk)TaxID, PhysicianName

T_ServiceCodes
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?


Help:

How do I accomplish this?

If each TaxID is the same as each ServiceCode you should be able to use an inner join. If you go to your query window you should be able to open the SQL view and use:
"SELECT p.physicianname , s.servicename, s.price from T_Physicians as t inner join T_ServiceCodes as s on p.TaxID = s.ServiceCode;"
Hope this helps.

[adigga1]

If each TaxID is the same as each ServiceCode you should be able to use an inner join. If you go to your query window you should be able to open the SQL view and use:
"SELECT p.physicianname , s.servicename, s.price from T_Physicians as t inner join T_ServiceCodes as s on p.TaxID = s.ServiceCode;"
Hope this helps.

I will try this code and post the results. So basically what will occur when a particular Tax ID is entered should bring forth and reference the associated Service table?

Thanks

[aas4mis]

I will try this code and post the results. So basically what will occur when a particular Tax ID is entered should bring forth and reference the associated Service table?

Thanks

This query will return 3 related fields. Physician, Service, and Price. The common link between the two is the TaxID and ServiceCode. So everything should match up (thanks to the inner join). If you want to filter for a specific field/value add that with a where clause and use it as your "row source" in your forms. Another option is creating the query and using the field from your form as part of your WHERE clause. Hope this helps.

[adigga1]

This query will return 3 related fields. Physician, Service, and Price. The common link between the two is the TaxID and ServiceCode. So everything should match up (thanks to the inner join). If you want to filter for a specific field/value add that with a where clause and use it as your "row source" in your forms. Another option is creating the query and using the field from your form as part of your WHERE clause. Hope this helps.

Thank you again for all of your help 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 and price....


SELECT T_Service.Servi ceNumID, T_Service.TaxID Num, T_Physicians.Ta xID, T_Physicians.Ho spitalName, T_Service.Sible yNum, T_SibleyCpt.CPT code, T_SibleyCpt.Cha rge, T_Service.ECCcp tNum, T_ECCcpt.CPTcod e, T_ECCcpt.Charge
FROM T_SibleyCpt INNER JOIN (T_Physicians INNER JOIN (T_ECCcpt INNER JOIN T_Service ON T_ECCcpt.ECCcpt Num = T_Service.ECCcp tNum) ON T_Physicians.Ta xIDNum = T_Service.TaxID Num) ON T_SibleyCpt.Sib leyNum = T_Service.Sible yNum;


thank you in advance
---adigga1