1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Building a simple access database

Discussion in 'Business Applications' started by caraewilton, Nov 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Hi

    Hoping someone can give me advise on the best table structure for a simple database I am building.

    Here is the scenario:

    I am a teacher. One of my most used resources is old exams for the subjects I teach. For a single subject I have old papers going back to 2002. This represents about 40 exam papers, currently all in pdf. I want to create a searchable database of the questions.

    For each question I have the following possible fields:

    Session
    Exam Year
    Component
    Topic
    Question Number
    Question
    Memorandum
    Notes

    My logic tells me I should have have a separate table for session, exam year and component as 1 table because one record will link to multiple questions. Then to link them using a code like 101111, first two digits being the year, the second two the session and the last two the component.

    question number, Question, memorandum and notes make up a second table with an additional field for code which links to table 1.

    A third table for topics as again one topic will be associated with multiple questions, but I do not know how to link this to table two :(

    Would this be the best way to set things up, or is there a better way?

    Another question, how much text can be placed in a single field entry. The memorandums for each question can be a lot, and I seem to recall access has a limit of 255 characters ... sure to exceed that...

    Lastly, some of the questions relate to a case study. Is there a way to link the question to a pdf of the case study?

    Any advise on the above or any thing else you may see is greatly welcome..

    Cara
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Cara, there are fairly simple rules to the design.
    1.If the data repeats a lot give it it's won table (like Subjects) to select from for the main table.
    2. where you 2 tables of multiple data that you want to join use a "Linking" Many to Many table.

    As to your question about the memorandums, use a Memo type field it has virtually no character limit (other than the size of the table & database).

    I can assist you with the design if you like and especially search section which can be very challenging.
     
  3. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Thanks OBP

    I had not really thought as far as the actual search part ... figured could just use a query with wild card criteria ... but no doubt will be asking for help.

    Of course, memo field, forgot about that one :)

    With reference to the table structure, so if I understand you correctly I am correct with my initial assumption of needing three tables, one that holds the unique question and memorandum fields, and then one for the session, component part and one for the topics...

    I am not so sure about the linking many to many table thing...

    I am using access 2010 by the by ... the last time I used access it was still 2003, so that also is proving a little challenge ...
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can't work with Access 2007/10, only 2003, so any search solutions that I supply would need to be based on a 2003 Form, the tables & queries are not a problem.
    A many to many table is just a table like a normal 1 to many table, but the many table is not a Main table and is used to join data together.
    So that you can join data from say Classes and Subjects, you can have many Classes which have many Subjects and it just contains their ID Key Fields plus any other relevant data.
     
  5. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    This is what I have so far. I have created screenshot as the format is 2010.

    I have created four tables:

    [​IMG]

    Table 2: Q & A is the main table:

    [​IMG]

    Code:
     links to "Table 1: Papers" which in turns stores fields for [Exam Year], [Session] and [Component]. I have added all the data for each record (exam paper) and created a drop down list of the the paper codes in "Table 2: Q & A".
    
    [Question Number] links to "Table 3: Case Study" which in turn hold an attachment to word files of corresponding case studies.
    
    I then created "Table 4: Curriculum" to hold the possible curriculum topics as well as which section of the syllabus and if a yes/no field to indicate if the topic pertains to A-Level students only. In "Table 2: Q & A" I have a combo box field which provides a drop down box listing the topics, using "Table 4: Curriculum" as the data source.
    
    As the screen shot above shows, "Table 2: Q & A" also contains the fields for question specific information like [Question Sub  Number] for example question a1, a2 etc; [Question] and [Memorandum].
    
    I have then created a form:
    
    [IMG]http://www.chouselive.co.za/database/six.jpg[/IMG]
    
    Now surprisingly all of this seems to actually be working. From the drop down list, I select the code, then I must add the question number, fill in the other information. Topic is also a drop down list and I can just tick which ever topics apply to the question. If I attach a case study, access automatically ascribes the case study to any record which has the same question number.
    
    So all good, but ...
    
    First Question:
    Have I set things up correctly, or is there a better way?
    
    Second Question:
    The Field [Question Number] requires that I manually insert the correct question number. I am using the following system:
    If the exam code is 101111 and the question is question 2 then the question number is the [code] and the number 2 like this: 1011112.
    Presently I am typing all of that in. Is it possible that I only type the 2 and access automatically adds the [Code] to the front to create the [Question Number]? If so, how would I achieve this?
    
    Many thanks
    Cara
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The only change that I would make is to the Relationships, they would normally be 1 to many going from the sub tables to the main table with the join as where rows are equal, this gives the One to Many relationship with referential integrity being a possibilty.
    You have it set as All records from the main table and only those from the sub tables, which might lead to problems later, BUT if it works leave it for now
    It might also be better to have the Code as the Main table and the Question details as a seperate table using the Code as it's Foriegn Key Field for linking, this avoids the need to add the Code and the Number to get a unique number.

    As to the Question, you can use simple vba to combine the 2 fields, but it might require a dummy Question field to hold the 2 which would be combined with the code to go in the actual Question field.
     
  7. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Hi OBP

    I am a little slow, can you explain to me exactly how I would do this. I.e. what columns do I need to move to which table?

    Thanks
    Cara
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have looked at your tables again and I think your Q & A table is OK, but you should not be creating the Question number with the Code as a prefix, because the Question Number appears to be coming from the Case Study table Index Key Field.
    So your Q & A table is the Many to Many linking table that I was talking about.

    What field in the Q & A table is the Answer?
    Also where does the Question Sub Number come form and what does it do?
    What is the Record Source of your Form?
    As I think the Question Number (which comes from the Case Study table) should be a Combo box
     
  9. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Ummm, each exam session has a 3 papers, and then each paper will have several questions like this:

    Paper 1
    1a
    1b
    2
    3a
    3b

    Paper 2
    1a
    1b i
    1b ii
    1c
    1d
    2ai
    2aii
    2b
    2c
    2d

    and so on...

    Now paper 1 just has questions.

    Paper 2 has 2 case studies. For each case study there are several sub questions as it where.

    So my thinking was, each exam needs a unique code. Then to differentiate between the different questions I was just going to use 1, 2 etc. But what about the case studies? I did not want to attach the case study to several different questions. So the code was already unique so by just adding the 1, 2 to the end of the code, it makes a new unique question number which I can then attach the case study to.

    The exam sub question is just if the question was a, b1, b2 or what ever of the main question. So I just type it in.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I am starting to get the picture.
    Do the Codes go in to the Papers table before the Q & A table or do you enter them in the Q & A Table.
    If it is the later we can put some VBA code in the Code's After Update event Procedure to populate the Question Number with the Code and then you can just add the actual number on the end.
     
  11. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    The codes are entered first in the papers table

    [​IMG]

    Then I am just using a drop down list in the Q and A table.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, it can still go in the after update event of the Combo, I would need the name of the combo and the name of the Question Number field as it is on the form.
     
  13. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    here is what happens in the Q & table:

    [​IMG]
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What about the Q & A form?
    Can I suggest that you enter the papers data in a Mainform and the Questions in a subform on that form?
    That gives you simultaneous entry.
     
  15. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Here the screen shots of my form

    [​IMG]

    [​IMG]

    [​IMG]
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025535

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice