Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009

    Unanswered: Please help me...

    Hi Forum,

    I posted a question last week but I did not get any suitable responses. I probably was not clear enough in what I wanted so I will try to be more informative.

    I am a novice at Access and am not able to program in SQL. I am just able to construct queries using the wizard or design view. I am looking for some help or SQL code in order to construct two queries to perform 2 important functions described below.

    The role of the database is to be a monitoring system for documentation. Although the documentation will not be held on the system, the administration of this documentation will all be done through this system.

    In respect of the documentation, I will only store 2 pieces of information which refer to the document in question. This is the Submission Date of the document (the date at which the document refers to) and the Site (the actual area the documentation refers to. For example, the Submission Date of Document x will be 'December 2008' and the Site will be 'UK.'

    Function 1.
    I would like the system to be able to record comments on the documentation. I have created a form where I can write my comments on a document, and I have created a form for someone else to write their comments in. In order to capture the information I have a table for each person that will be commenting:

    So there is a table called Michael:
    Site (this is the field for the actual area the documentation refers to).
    Submission (the date of which the document refers to)
    Comments (a memo field where I can write as much as I like about the content of the document).

    And there is a table called Jerry (where the structure is exactly as above):
    Site (this is the field for the actual area the documentation refers to).
    Submission (the date of which the document refers to)
    Comments (a memo field where I can write as much as I like about the content of the document).
    and a table called Jerry:

    Sample Data for Michael Table (I will put in brackets the name of the field, followed by the content):
    [Site] UK, [Submission] Dec 08, [Comments] Very Good.
    [Site] Germany, [Submission] Dec 08, [Comments] Very Bad.

    Sample Data for Jerry Table (again as above)
    [Site] UK, [Submission] Dec 08, [Comments] Quite Good.
    [Site] Germany, [Submission] Dec 08, [Comments] Quite Bad.

    I now need to create a query so that when prompted for the Site and Submission, all comments made will be displayed. I need it to display all comments even if there is no comment made. For example, if Michael makes a comment on UK Dec 08 document and Jerry doesn't, the query still needs to work.

    I have tried in design view and can not get it to work properly. No matter how I do it I just can not get it to work. I can't keep all the data in one table because when I go to run a report it displays the information all wrong - it needs to be in different tables. One person will only ever have one comment on one type of Site and one Submission. Eg for the Dec 08 document for UK, Michael will only have one comment. Jerry will only have one comment.

    I hope that I have explained this clearly. It is not a very complicated thing to do ( I think), I just cant figure out how to do it!

    Function 2
    Progress Monitoring. I want to be able to monitor / record if a Site has submitted to us at a date.
    For example, UK and Germany must submit quarterly. So we must receive Dec, Mar, Jun and Sep documents. Any ideas how I can incorporate this and what I need to do.

    If anyone could be help me I would be eternally grateful. I have been playing around with it and I am just not intelligent enough to get it working. I have been able to do other things such as construct a list of contacts (which was easy to do and quite important), but these two functions are absolutely essential. If someone could help me I would be so happy.


  2. #2
    Join Date
    Apr 2004
    metro Detroit
    You need to revisit your table structure. You do not want to have a separate table for each person. Use one table with a field for the person commenting. Once you do this, you should be able to easily set up the needed queries. Post back if your still having problems.

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    You never want to create a table for each "person" so to speak. This is not good design and will only make your life much, much more difficult for ANY queries. You have to think of it in terms of "grouping" fields. If you had 100 employee's, would you really want to create 100 tables? As a general rule, group wherever and whatever you can into fields in a single table to avoid multiple tables.

    For example:


    Then you just apply criteria to the query to filter the records you need.

    If data starts becoming repeatitive (ie. John Doe is the first record with all his other data and John Doe is also the 2nd, 3rd, 4th, etc. record because of other fields), THEN it's time to start thinking relational.

    Also, another good rule to follow - NEVER put spaces or odd characters (*,~'&) in table names or field names! It will again, make your life much easier. And data which holds date data (ie. 4/1/09) should always be in a Date/Time data type field (not a text field.)
    Last edited by pkstormy; 07-06-09 at 19:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2009
    British Columbia, Canada
    I would recommend getting a book on relational database concepts or contracting your work out to a professional.

  5. #5
    Join Date
    Jul 2009

    Smile You can use mutable but it is messy

    When you have two or more table and you need information from one table that is not included in the other tables you need to change the relationship to show a null. The problem you have is you do not know if the data is missing from Michael or Jerry or Tom (if you have more than two) so you will need to create a “Union” query to get the answer you are looking for. In design for the query double click on the line that connects two tables in your case there should be more than one. I’m thinking you have them connected from site to site and date to date. Once you double click on the line, ( a box will appear) choose option 2 and click ok. Do this for all the connection lines in your query. If you have more than two tables all the lines must come out of the same table. Change the view to SQL and copy the code to a new query and type in “Union” Now go back to the copied from query and change the view back, If you have only two tables double click on the connecting lines and choose option 3 copy the SQL and past it into the new query and save it then run it. If you have more than two tables then you will use only option 2 and you will need to connect as follows. If you have three tables connect table one to table two and table one to table three double click on the line and choose option 2, copy and paste the SQL and type Union, delete the lines and connect table two to table one and table two to table three double click on the lines and choose option 2, copy and paste the SQL under the union in the new query . Delete the connections again and still using option 2 connect table three to table one and table three to table two, copy and paste the SQL. Save the query! Then run it. It will have nulls if not everyone has commented. The query will require some clean up, if you need help with that post again.

Posting Permissions

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