Problem Creating Query

Hi,

I have created the following query:

SELECT [Economic Capital and Stress Testing].Comment, [Regulatory Issues].Comment, [Risk Appetite].Comment, [Trans-risk Diversification and Concentration Risk].Comment, [Economic Capital and Stress Testing].[ICAAP Submission], [Economic Capital and Stress Testing].Site
FROM (([Economic Capital and Stress Testing] INNER JOIN [Regulatory Issues] ON [Economic Capital and Stress Testing].[ICAAP Submission]=[Regulatory Issues].[ICAAP Submission]) INNER JOIN [Risk Appetite] ON [Regulatory Issues].[ICAAP Submission]=[Risk Appetite].[ICAAP Submission]) INNER JOIN [Trans-risk Diversification and Concentration Risk] ON [Risk Appetite].[ICAAP Submission]=[Trans-risk Diversification and Concentration Risk].[ICAAP Submission]
WHERE ((([Economic Capital and Stress Testing].[ICAAP Submission])=[Enter Submission]) AND (([Economic Capital and Stress Testing].Site)=[Enter Site]) AND (([Regulatory Issues].[ICAAP Submission])=[Enter Submission]) AND (([Risk Appetite].[ICAAP Submission])=[Enter Submission]) AND (([Trans-risk Diversification and Concentration Risk].[ICAAP Submission])=[Enter Submission]) AND (([Regulatory Issues].Site)=[Enter Site]) AND (([Risk Appetite].Site)=[Enter Site]) AND (([Trans-risk Diversification and Concentration Risk].Site)=[Enter Site]));

This query only works if information has been inputted in each of the 4 tables (EC, Trans-Risk, Regulatory and Risk Appetite).

Is there a way of making this query work even if information has not been inputted into one of these tables?

Thanks!!

Hi,

Without seeing a sample of your data and a description of your tables its hard to understand exactly what you are trying to achieve, but I would recommend that you use an OUTER JOIN instead of an INNER JOIN.

The outer join will return all rows from one table even if no corresponding record exists in the other table. Check out this definition of Join types

Hello!

Thank you very much.

I am sorry I should have been more specific.
The job of the database is to pass comments on certain documents by various colleagues. Eg. A Dec Report is available, I want to write my comments, then I want a colleague to give her comments, then another colleagues gives his etc. I then want to create a report so that all comments on this document can be sent back to the people who gave us the report in the first place.

I have a table for each colleague who will be responding, including the date of the document, the document owener and the comments made. Here is a sample:

Economic Capital and Scenario Analysis (Table which will be for my comments)
ICAAP Submission (the date of the document which I will be writing about)
Site (the area which the document relates to)
Comments (the comments I will be making).

I did try to put all of this information in one table, but it was impossible to create a report!! I think this is the only way to do it, but I am not sure how to make the query good enough!

Thanks once again!

Hi,

I'm not sure if I understand your requirement correctly but I would recommend that a link table would be a better approach to take. eg:

tblReports:

report_id autonumber
report_detail text

tblComments:

comment_id autonumber
comment_detail text

tblUsers:

user_id autonumber
user_forename text
user_surname text

tblLink:

report_id number
comment_id number
user_id number

The job of the link table would be to link everything together, heres some sample data:

tblReports:

report_id report_detail
1 Economic Capital and Stress Testing
2 Regulatory Issues
3 Risk Appetite

tblComments:

comment_id comment_detail
1 Great report
2 Very informative
3 Didnt enjoy it

tblUsers:

user_id user_forename user_surname
1 Joe Bloggs
2 Jane Doe

tblLink:

report_id comment_id user_id
1 1 1
1 2 2
2 3 1

Then to grab the comments for a given report:

I hope this helps, sorry if I have missed the point.

Dear Sync or Swim,

Thank you so muich for your reply and efforts. Your intelligent response was a bit over my head and I am not quite sure how to adapt the 'link table concept to my problem. I am really sorry and I should have been clearer about my intentions. I will now try to explain clearly.

The role of the database is to capture comments made by various areas of my team, on certain reports. Once comments have been made on a report by the various members of my team, I would like to create a memo (or short report), to respond to the people who gave us the report.

If we take an example, the report will be 'UK Branch ICAAP' and the date of the report is 'Dec08'. The team areas which will comment on this report will be 'EC', Reg', 'R.App' and 'Meth'. What I did was create 4 tables - 'EC', Reg', 'R.App' and 'Meth'. Each table contains 3 fields - Submision (date of report) Site (UK Branch) Comments (a memo containing the comments).

I would like to create a query that will prompt for the Submission and the Site, and then pull out all comments made.

I will then be able to create a report containing all of the various comments.

Once I have the basics sorted I will be able to expand the database to make it more helpull (e.g. contain a field for progress so as to monitor if a Site is taking our comments on board).

Based on the above, is this possible?

Once again, thank you so much for your help so far.
Michael

Sorry Sync or Swim,

I forgot to mention that the data in each table is linked by Submission (the date, which is usually quarterly or semi-annually) and also the site.

E.g

EC
Submission Site Comment
Dec08 UK txt
Dec08 NY txt
Dec08 HK txt
Jun08 UK txt