Results 1 to 11 of 11
  1. #1
    Joshwenny is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    11

    Need Help


    I am an auditor and I have 6 different types of audits that I perform. I have created 6 different tables and 6 different forms to fill those tables. How do I go about getting the results of each of those tables together. For instance I have a score for each audit, I want to be able to have a report that collects the audit score from all 6. I’m assuming I need to link those 6 tables somehow and run a query but am unsure of how to go about this. Any help would be greatly appreciated. Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us in plain English about the audits and what info you collect.
    What exactly differentiates audit 1 from audit 4 for example?

    What is the result/purpose of bringing all audits together with your "query"?

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Use a UNION query:
    "SELECT AUDIT_NAME, AUDIT_SCORE, AUDIT_DATE FROM tblAUDIT1
    UNION
    SELECT AUDIT_NAME, AUDIT_SCORE, AUDIT_DATE FROM tblAUDIT2
    UNION
    SELECT AUDIT_NAME, AUDIT_SCORE, AUDIT_DATE FROM tblAUDIT3
    UNION
    ..............................
    ..............................
    SELECT AUDIT_NAME, AUDIT_SCORE, AUDIT_DATE FROM tblAUDIT6;"

    Cheers,
    Vlad

  4. #4
    Joshwenny is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    11
    Thank you for your response Gicu, I will check to see if that will work.

    Orange, the six audits all contain an auditors name. a date which the audit was performed, as well as, a bunch of yes and no questions to which I give an answer. Each table is a different set of questions. I have within those tables created a field to add the yes and no answers to return a score, For example, I have 5 questions that based on how I answer the question, "yes", "no", "N/A" will calculate a score. I did this by creating a field called [Question 4 Correct] calculated using (IIf([Question 4-1]="Yes",1,0))+(IIf([Question 4-2]="Yes",1,0))+(IIf([Question 4-3]="Yes",1,0))+(IIf([Question 4-4]="Yes",1,0))+(IIf([Question 4-5]="Yes",1,0)), I then created a field called [Question 4 Incorrect] which is the same calculation but the Yes is now No. I then created another field called [Question 4 Score]. This is created by taking [question 4 correct]/([question 4 correct]+[question 4 incorrect]). So what I'm trying to do create a query that will pull the [question 4 score] from each of the 6 tables into one query. Ideally I would be able to type in an auditor's name, as well as a date range and pull those scores from all 6 tables. Your help with this is greatly appreciated.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You say these are 6 different types of audits but are the tables identical in structure? If yes, then should definitely be one table with another field for the audit type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Joshwenny is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    11
    They are not identical in the fact there are different questions asked on all 6 tables.

  7. #7
    Joshwenny is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    11
    I've broken down the 6 tables into 6 queries with a underwriter field, a date field, and a score field. How can I get all the six scores from all 6 different queries into one report?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    UNION query as suggested in post 3 or subreports.

    I expect your data structure could be more normalized. This would mean field for QuestionID and another field for the response. There would be 3 tables: 1 for the audit general info, a dependent table with AuditID_FK, QuestionID, Response, and a lookup table for the questions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Joshwenny is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2018
    Posts
    11
    I would love to have a form that would update a set of questions based on the audit I'm performing. I would have no idea on how to go about that. I then could just utilize one table instead of the 6 which would be ideal! let me know if this is a possibility. ideally I would go into a form, type in the underwriter I'm auditing, the date, and the type of audit which would then update the form to the specific questions for that audit type.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    During conventional data entry, user would select question from combobox then in another control enter response. If you want to 'prefill' table with a standard set of questions relevant to the new audit, that requires code running an INSERT SELECT sql action. Fairly common topic. The more 'user-friendly' the more code.

    Otherwise, if you retain current structure, use one of the already given solutions.

    For future threads, should use more informatve title. We presume you need help. You might still be able to edit this title.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Based on OP's description, I would go with tables:
    tblQuestions: QID, QuestionTxt, [AnswerType] [wehre AnswerType is needed, when there may be different types of answers (text, number, TRUE/FALSE)];
    In case answers for questions are choices from predefined lists, then you need additional table
    tblQuestionAnswerLists: QALID, QID, Answer, IsCorrect; (In case there may be diffrerent types of answers, instead of field Answer you need the set of fields e.g. AnswerTxt, AnswerNum, AnswerYes, where AnswerType from tblQuestions determines which answer field is used);
    tblAuditDef: AuditDefID: ADID, ... (This table defines certain type of audit).
    tblAuditQuestions; AQID, ADID, QID (Determines the set of questions for certain types of audit);
    tblAudit: AID, ADID, AuditDate, Auditor, ... (The registry of audits);
    tblAuditAnswers: AAID, AID, QID, [AnswerType], [Answer]/[AnswerTxt, AnswerNum, AnswerYes] (When included, then the value of AnswerType is calculated from tblQuestions. The field IsCorrect which value is calculated from tblQuestionAnswers may be included too, to make report generating easier).

Please reply to this thread with any new information or opinions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums