[Gnumed-devel] Contacts PNG and SQL for you

[richard terry]

Hi David,

As discussed by phone, this query is one of scores from a contact manager 
database I wrote for the Hunter Urban Division some years ago. It does far 
more than just keep company names and addresses, as they use it for their 
doctor database etc. 

The program is used on an NT network connecting some 40 machines of many 
different Mhz. If you check the middle of the sql there are something like 15 
right joins! Phoebe their IT manager says that at any one time the program is 
open by 20 odd users all using it and they havn't experience a speed problem, 
and they love the interface.

Perhaps it is an odd database design because it hinges around a data_links 
table which gives enormous flexibility. Having no computer 
education/training/being self taught, I have no idea how badly this violates 
basic design principals.

This query just pulls out the names or organisations and employees, and 
presents the data as shown in the contacts_details.png, the viewing port 
shown in this can be maximized shown on contacts.png. All this data is public 
property being on the hunter area health service directories/web sites/local 
telephone books so i don't think posting it to the list would be a problem.

The visual basic client is of course the front end to the ms access 7.0 
database. In gnuMed one could put the front end on a separate tab or as a 
separate program, but the internals of the referrals etc would just directly 
access the tables.

              SELECT data_links.Link_ID, data_links.Deleted, 
data_links.Last_Updated, data_links.GP_PersonSpecific_ID, 
data_links.GP_PracticeSpecific_ID, data_links.GeneralPracticeSpecific_ID, 
data_links.Preferred_Address, data_links.Organisation_Communication_ID, 
data_links.Employee_Communication_ID, data_Communications_2.Work_Phone AS 
Employee_Work_Phone, data_Communications_2.Work_phone_confidential AS 
Employee_Work_phone_confidential, data_Communications_2.Fax AS 
Employee_Work_Fax, data_Communications_2.Fax_Confidential AS 
Employee_Fax_Confidential, data_links.Network_ID, data_links.Associate_ID, 
data_links.Memo_ID, Data_Organisations.Description, 
data_links.Organisation_ID, data_links.Address_ID, 
data_Communications_1.Work_Phone, 
data_Communications_1.Work_phone_confidential, data_Communications_1.Fax, 
data_Communications_1.Fax_Confidential, data_Communications_1.email, 
data_Communications_1.email_Confidential, data_Communications_1.internet, 
data_Communications_1.internet_Confidential, data_Communications_1.mobile, 
data_Communications_1.mobile_confidential, lu_categories.Category_ID, 
lu_categories.Category, Data_Addresses.[Street1] & " " & [Suburb] AS Address, 
Data_Addresses.Street1, Data_Addresses.Street2, Data_Addresses.Street3, 
Data_Addresses.Suburb, Data_Addresses.Postcode, Data_Addresses.State, 
Data_Addresses.Head_Office, Data_Addresses.Postal_Address, 
[Data_Persons].[Firstname] & " " & [Surname] AS Name, Data_Persons.Surname, 
data_links.Person_ID, Data_Persons.Firstname, Data_Persons.Title, 
Data_Persons.Sex, Data_Persons.Birthdate, Data_Persons.Deleted AS 
Persons_Deleted, data_links.Person_Occupation_ID, lu_Occupations.Description 
AS Occupation, data_links.Person_Category_ID, lu_categories_1.Category AS 
Person_Category, Data_Persons.Person_Communication_ID, 
Data_Communications.Home_Phone, Data_Communications.Home_Phone_Confidential, 
Data_Communications.Work_Phone AS Person_Work_Phone, 
Data_Communications.Work_phone_confidential AS 
Person_Work_phone_confidential, Data_Communications.email AS Person_email, 
Data_Communications.Fax AS Person_Fax, Data_Communications.email_Confidential 
AS Person_email_Confidential, Data_Communications.Fax_Confidential AS 
Person_Fax_Confidential, Data_Communications.internet AS Person_internet, 
Data_Communications.internet_Confidential AS Person_internet_Confidential, 
Data_Communications.mobile AS Person_mobile, 
Data_Communications.mobile_confidential AS Person_mobile_confidential, 
Data_Associates.Member_Type_ID, Data_Associates.Inactive, 
Data_Associates.DueBack, Data_Associates.HunterGP, 
Data_Associates.Email_Delivery, Data_Associates.Deleted AS Associate_Deleted, 
Lu_Networks.Network_name, data_memo.Memo, 
data_GP_PersonSpecific.QA_Ref_Number, data_GP_PersonSpecific.Interests, 
data_GP_PersonSpecific.Experience_ID, data_GP_PersonSpecific.full_Time, 
Data_Gp_PracticeSpecific.Position_ID, Data_Gp_PracticeSpecific.Sessions, 
Data_Gp_PracticeSpecific.Payment_To, Data_Gp_PracticeSpecific.Payment_Method, 
lu_Occupations_1.Description AS Position, 
Data_GeneralPractice_PracticeSpecific.Pooled_Hours, 
Data_GeneralPractice_PracticeSpecific.Signed_Agreement
FROM (((((((((((((((Data_Addresses RIGHT JOIN data_links ON 
Data_Addresses.Address_ID = data_links.Address_ID) LEFT JOIN 
Data_Organisations ON data_links.Organisation_ID = 
Data_Organisations.Organisation_ID) LEFT JOIN Data_Persons ON 
data_links.Person_ID = Data_Persons.Person_ID) LEFT JOIN Data_Associates ON 
data_links.Associate_ID = Data_Associates.Associate_ID) LEFT JOIN Lu_Networks 
ON data_links.Network_ID = Lu_Networks.Network_ID) LEFT JOIN data_memo ON 
data_links.Memo_ID = data_memo.Memo_ID) LEFT JOIN data_GP_PersonSpecific ON 
data_links.GP_PersonSpecific_ID = 
data_GP_PersonSpecific.GP_PersonSpecific_ID) LEFT JOIN 
Data_Gp_PracticeSpecific ON data_links.GP_PracticeSpecific_ID = 
Data_Gp_PracticeSpecific.GP_PracticeSpecific_ID) LEFT JOIN lu_Occupations AS 
lu_Occupations_1 ON Data_Gp_PracticeSpecific.Position_ID = 
lu_Occupations_1.Occupation_ID) LEFT JOIN data_Communications AS 
data_Communications_1 ON data_links.Organisation_Communication_ID = 
data_Communications_1.Communication_ID) LEFT JOIN Data_Communications ON 
Data_Persons.Person_Communication_ID = Data_Communications.Communication_ID) 
LEFT JOIN Data_Communications AS data_Communications_2 ON 
data_links.Employee_Communication_ID = 
data_Communications_2.Communication_ID) LEFT JOIN lu_Occupations ON 
data_links.Person_Occupation_ID = lu_Occupations.Occupation_ID) LEFT JOIN 
lu_categories ON Data_Organisations.Category_ID = lu_categories.Category_ID) 
LEFT JOIN lu_categories AS lu_categories_1 ON data_links.Person_Category_ID = 
lu_categories_1.Category_ID) LEFT JOIN Data_GeneralPractice_PracticeSpecific 
ON data_links.GeneralPracticeSpecific_ID = 
Data_GeneralPractice_PracticeSpecific.GeneralPracticeSpecific_ID
WHERE (((data_links.Deleted)=False) AND ((data_links.Organisation_ID)<>0) AND 
((lu_categories.Category)<>"General Practice"))
ORDER BY Data_Organisations.Description, data_links.Organisation_ID, 
data_links.Address_ID, Data_Persons.Surname, data_links.Person_ID;

contacts_details.png
Description: PNG image

contacts.png
Description: PNG image