right, I have been trying to get to the point of this question for the last 2 weeks, but haven't really been able to put my question across correctly. Well now I think I may have enough knowledge of the actual requirement to my solution (I think ) to pose a clear question.
Ok, I have two tables, one that contains a list of hospitals, and one that contains a list of journey's in which a hospital has carried out (by tranferring patients). In my 'Journey's' table I have two columns that act as Foreign keys to the 'hospital' table. These two Foreign key based columns are 'fromhosp' and 'tohosp'. Now then, my query is to perform an accumilation of the journeys made by determining whether a journey was made to a hospital (which adds 1 to the hospital specified in the 'tohosp' column, and then if a journey was made from a hospital 1 will be added to the hospital in the 'fromhosp' column - these accumiltations are not a problem).
The problem lies in when I try to group the results by hospital, as there are 2 Foriegn keys that link to the same table (Hospitals table), and so when I attempt to make the link from the Journeys table to the 'Hospitals' table I end up linking to only on of the columns (for example the 'tohosp' column) and therefore will not get the results associated with the other column (for example the 'fromhosp' column).
I need to know if there is a way getting both these columns to link to the hospitals table so that I can produce a report that is consist for all the journeys in the 'Journeys' table.
I have been pulling my hair out over this for weeks , and would really appricate it if someone could at least point me in the right direction.