I have two tables that have have slightly different types of data relating to two different areas of our business financial support and equipment support. However each table does have one column that sits in both tables, an individual id column.
What I would like to do is combine the information from both tables in a query to give me an overall view of the support an individual has been provided with.
For instance; Financial Support will have columns for..... 'Support ID', 'Personal ID', 'Amount Requested', 'Date Agreed', 'Support Type' Equipment Loan will have columns for...... 'Loan ID', 'Personal ID', 'Date Loaned', Equipment Type'
I envisioned the results set to look something like below, where the Personal ID column from both tables is merged into one column.
It's likely that any individual may have many or none of either Financial or Equipment support records so there is likely to be multiple rows for each individual and each row will have one corresponding Financial Support ID or Equipment Loan ID.
I have attempted various permutations of Union etc but can't seem to get the results I'm looking for. Am I looking for the impossible?
This is nearly there, however I'm getting duplicated results. For instance if an individual has 2 Financial records and 3 Equipment records there are 6 result lines generated, whereas I was hoping for was to get 5 rows.... 2 for the Financials and 3 for the Equipment. And where there is Financial detail there would be no Equipment detail and then visa versa.