Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts
    First, please forgive the title to this thread. I donít know what to call this problem. Iíll attempt to explain it and hope for the best. I have a table of records (tasks) with information on each record (like, duh). For every record (task) I have a set of 25 questions that must be answered. I also need an indicator to tell me the phase of completion: not started, in process, completed. I started with 25 memo fields in the main table, one for each question. I now have to create a text or number field for each question for the phase (yes/no wonít work). Doing it this way seems too cumbersome. Iím hoping for an easier fix. Can someone give me suggestions and/or direct me to some on-line help?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by DCOLES View Post
    First, please forgive the title to this thread. I donít know what to call this problem. Iíll attempt to explain it and hope for the best. I have a table of records (tasks) with information on each record (like, duh). For every record (task) I have a set of 25 questions that must be answered. I also need an indicator to tell me the phase of completion: not started, in process, completed. I started with 25 memo fields in the main table, one for each question. I now have to create a text or number field for each question for the phase (yes/no wonít work). Doing it this way seems too cumbersome. Iím hoping for an easier fix. Can someone give me suggestions and/or direct me to some on-line help?
    Why not have a record for each question, linked back to the Tasks table.

    If there is a status field per question then that can go into this table as well.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    How about:

    Table Tasks
    Key field = TaskNo
    Identifying fields - how ever many you need of any type:
    e.g. Assigned to: Text - Due Date: Date - Budget: Currency, etc.


    Table Questions
    Key field = Question No: Integer {Byte?}
    Question Text: Test {Memo}


    Table Answers
    Key Field = AnsNo: Number
    Foreign Key = Tasks!TaskNo
    Foreign Key = Questions!Question No
    Answer Field = Answer: Integer {Byte}

    This structure (with modifications as necessary) would let you set up master/sub forms to gather the information. The sub form could be generated in code to display the questions and current answers.

    This is just a quick stab, let the carnage begin.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree with RetiredGeeek and Patt.

    A couple of additions:
    From the original question I think the Answer needs to be a memo rather than a number.
    The Indicator field needs to be added to the Anwers table.

    For every task, you need 25 records created in the Answers table. I would write code to do this, and use the After Insert event for tasks, to run it.

    Something like this, if there are 25 Questions, numbered 1 to 25.

    Code:
    Private Sub sbCreateAnswers()
    	Dim db 	As DAO.Database
    	Dim rs 	As DAO.Recordset
    	Set db = CurrentDb
    	Dim intcount As Integer
    
    	Set rs = db.OpenRecordset("tblAnswers", dbOpenDynaset)
    	For intcount = 1 To 25
     	rs.AddNew
     	 rs("TaskID") = Me.TaskID
     	 rs("QuestionID") = intcount
     	rs.Update
    	Next intcount
    	rs.Close
    	Set rs = Nothing
    	Set db = Nothing
    
    End Sub
    Regards
    John



  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Right you are! I forgot the table with the status titles, Answer was a number to hold the key of status titles table so you're not storing all that text, although you could store the status titles if you want.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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