By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

How to Reference Data in a different Table?

P: 29
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?
Sep 20 '08 #1
Share this Question
Share on Google+
4 Replies


aas4mis
P: 97
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.
Sep 20 '08 #2

P: 29
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
Sep 20 '08 #3

aas4mis
P: 97
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.
Sep 22 '08 #4

P: 29
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.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;


thank you in advance
---adigga1
Oct 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.