Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: ICAAP comments optional (was once Help, please!!)

    Hi,

    Background

    I am wondering if anyone can help me. I am a bit of a novice at Access and I am trying to design a simple database. I want a system where various pre-defined users can input comments about documents. I want to store this information in the database and then later create reports.


    My tables contain 3 fields:
    ICAAP Submission (a date at which the document which will be commented on refers to)
    Site (the owner of the document)
    Comment (a memo field where comments are entered regarding the document)

    I have a few of these tables for the few people who will be entering comments about these documents.

    Problem
    I have created the following query, which does not work properly:

    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], [Regulatory Issues].[ICAAP Submission], [Risk Appetite].[ICAAP Submission], [Trans-risk Diversification and Concentration Risk].[ICAAP Submission], [Economic Capital and Stress Testing].Site, [Regulatory Issues].Site, [Risk Appetite].Site, [Trans-risk Diversification and Concentration Risk].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 (([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 (([Economic Capital and Stress Testing].Site)=[Enter Site]) AND (([Regulatory Issues].Site)=[Enter Site]) AND (([Risk Appetite].Site)=[Enter Site]) AND (([Trans-risk Diversification and Concentration Risk].Site)=[Enter Site]));

    Basically, I want the query to prompt the user for two pieces of information; ICAAP Submission and Site. The problem is that I want the query to select these conditions in 4 different tables. I also want this query to work such that it will work even if no comment is made for a particular ICAAP Submission for a Site (i.e. if only 3 people make comments from 4, I need the query to work).

    I hope I have been as descriptive as possible. Any help would be greatly appreciated!

    Thanks!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Moved from the Mew Members and Introductions forum to the Microsoct Access forum to increase your chance of getting a useful answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    cheers patp!!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why not have one table? Just add a field called StaffMember or something. Then you won't need to deal with the complexities raised by such bad table design
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2009
    Posts
    8
    Hi!

    Thanks for replying.

    I tried it this way also but encountered problems.

    1) I can create a query catching the data I want, however it only works properly if each 'comments' section has been filled in. Here is my query:

    SELECT [All Comments].Submission, [All Comments].Site, [All Comments].[EC Com], [All Comments].[Reg Com], [All Comments].[Str Com]
    FROM [All Comments]
    WHERE ((([All Comments].Submission)=[ICAAP Date]) AND (([All Comments].Site)=[Enter Site]));

    The table is All comments.
    Submission is the date of the document
    Site is the owner of the document
    EC Com - a comments section
    Reg Com - another comments section
    Str Com - another comments section

    2) How is it possible to create a report which shows the submission date, the site and comments made by the respective staff members. I have tried and it is not possible based on the above query.

    Many thanks!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

  7. #7
    Join Date
    Jun 2009
    Posts
    8
    Thank you very much.

    I have viewed the links and I can see how the join operation will be useful, but after trying myself I just can get it working. Could you possibly help me with the syntax of the JOIN expression in this case?

    I am afraid I am not a programmer and do not know where to start! I only built this query in design view and then viewed as SQL to show the forum my query!!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK sdo your problem (as I understand it) is that you are not getting all the rows your expect
    so you need to use the correct join
    Access join syntax is unique to access
    a left join will return all records from the the table named on the left of the join


    eg
    select my,column,list from my table
    left join anothertable on mytable.columnname=anothertable.othercolumnsname
    order by acolumn

    which will return all records from mytable, and any columns from anothertable which match the join criteria.

    what I'd suggest you do is try to create the query yourself, and come back if or as you have a problem

    if you want to see an example then use the query wizard to create a template you can modify
    Last edited by healdem; 06-30-09 at 10:04.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •