Results 1 to 4 of 4
  1. #1
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21

    Best Approach?

    Hi all


    So I have a dilemma on how to approach this, disclaimer i am by no means an access expert.

    I have a very time consuming manual form I have to go through on separate occasions (concept, design, implementation, sign off) when progressing through a project..

    Essentially it has 2 parts:
    A single front page table (dates, participants, project titles etc) which is updated briefly on the occasions it is opened but the project title etc remains the same:
    And a series of Yes/No/NA questions (250 of them which are the same every time).

    The idea is to eliminate all the No answers to either a yes or a NA.. (health and safety box ticking exercise Grrr)

    Instead of tabbing through some small fonted word doc that was written badly in the 90's I want to try to create a simple DB that allows me to bring up a form and tab through the displayed questions and click the answer as a Yes/No/NA.

    My issue comes when i have to go through the same Yes/no/NA questions that follow each stage.
    I started looking at each question as an individual field but i found the the questions are just too long for a sensible field name.
    This was basically my limit of Access knowledge and I was stumped on how to approach it so i thought i would ask before going down the wrong path.

    I can easily go into excel and create something where I tab down each line and add in a cross on the relevant column if this is though to be more appropriate, but i really would like to use Access (2010) and brush up on my skills at the same time as its been a while since i used it last.
    Thanks all

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You won't be able to (and shouldn't) store 250 + field as your questions, simply because access has a 256 limit on the number of fields in a table.

    What you should do is create a table for your questions with simply 2 or 3 fields -
    QuestionID - Autonumber PK
    QuestionText - Either text or long text depending on the length.
    ActiveFlag - Yes/no field to indicate if the question is still in use - I'm guessing they could change over time

    Then create another table (junction) to record the answers per project.
    AnswerID - Autonumber - PK
    ProjectID_FK - FK linked back to the Project header ID
    QuestionID_FK - FK to get the question number
    AnsResult - Text ? Defaults to No, create a simple combo box with Yes or N/a in it to record the answer.

    Then in your form you have a continuous sub form to display the questions.
    You will probably want so create some code to auto populate the questions whenever a new project is added, but I'll leave that for some google searches
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Looking at OP's starting posting, probably there is need for another separate table(s) too. Unless every project has only a single participant, there must be:
    a) When same person participating in several projects is an exception, one table is enough - tblProjectParticipants: ProjectParticipantID, ProjectID, ParticipantForeName, ParticipantLastName;
    b) Otherwise OP needs 2 additional tables.
    tblParticipants: ParticipantID, ParticipantForeName, ParticipantLastName
    tblProjectParticipants: ProjectParticipantID, ProjectID, ParticipantID

  4. #4
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21
    Thanks Minty... Just to add some confusion to the mix.. my 2010 version has the expected 255 character limit on the Text data type, however when switching to Memo, i lose the Field Size option in the field properties and it appears to limit the characters to around 50!

    Edit: so what i just realized is that the limit is in the Field Name, not the data entered into that field!

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

Similar Threads

  1. Best approach
    By gwboolean in forum Access
    Replies: 2
    Last Post: 08-31-2017, 07:43 PM
  2. Need direction on best approach
    By mrcarter in forum Queries
    Replies: 8
    Last Post: 01-23-2014, 06:05 PM
  3. Is DLookup the Best Approach for This?
    By Tim777 in forum Access
    Replies: 6
    Last Post: 11-20-2012, 01:25 PM
  4. Best Approach to learn VBA
    By Richie27 in forum Access
    Replies: 3
    Last Post: 06-16-2012, 01:33 PM
  5. Not sure how to approach this problem
    By Jasrenkai in forum Access
    Replies: 2
    Last Post: 02-28-2011, 05:23 PM

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