Full Version: population of a table?
UtterAccess Forums > Microsoft® Access > Access Forms
jofoxy2000
I wish to create a voting database. But there are different sessions to vote on. What I would like to be able to do is; instead of creating a new table from the back end of the database, i wish to be able to create a new table of records from a form front end. Like an add record form. The problem i got is i want to be able to add a number of questions and answers. For e.g. vote on a TV show. There maybe 5 questions to vote on in one session day, and 7 questions on a separate voting session day. Can this be achieved or will each session table will have to be created manually?
Please help
jofoxy
argeedblu
Jofoxy,
From what you have described, it sounds like you may not have conceptualized your data structure in an optimal and normalized way. Having separate session tables would prove to be very cumbersome over time. A two table combination of a sessions (master) table and a sessions questions table would allow you to maintain a continuous record of your sessions and question.
In the sessions table you would have a primary key to identify individual session records, a session date field, and other fields that would describe the session.
In the sessions questions table, you woud have a primary key to identify the question, a foreign key to identify the session to which each question relates, and a question field to contain the text of the question.
Using this structure you would add as many session question records as are necessary for each session. A simple query would then allow you to select and display the questions that apply to any particular session.
It will be far less troublesome in the long run to store this data in the backend.
In case you are not familiar with relational database design, here is a list of links that will give you background information to guide you in the development of your database:
jofoxy2000
I Understand sort of, each session can have a number of questions..... the session is the customer and the questions and answers are the number of orders.
Thanks for your help....
regards
jofoxy
argeedblu
Jofoxy,
general rule of table design is that there should be only one table for each 'thing.' Each record in a table represents one instance of the 'thing' the table describes. So, whether you call the 'things' sessions or customers, they should have their own table. In naming your tables and your data, calling a spade a spade will make it much easier for you to describe, and others to understand, what you are doing.
However, regardless of what you call each distinct 'thing,' each 'thing' deserves its own table. Secondly, all instances of each 'thing' should be stored in that 'thing's' table.
So are you dealing with customers and orders (and therefore, items ordered) or are you dealing with sessions, questions, and answers?
Glenn
jofoxy2000
i am trying to write a database... to record questions and answers to a teaching sessions. its just the teaching sessions are repeated throught the year, just the number of questions may change or the qestion order. i wanted the user of the database to be able to enter a question and its voting answer e.g. 5 on a form for a teaching session on a particular date. its just if i put 10 boxes for questions, it may not be enough or to many. i need a table to popluate with questions on demand.
understand i should link the tables... table for teacher details... table of sessions and date... then a table of
questions
then run an SQL... to bring back a teacher--- session--- questions asked--- answers to questions --- but a teacher may teach more than one session and on diffrent dates. i will be seaching by teacher for the results.
regards
jofoxy
argeedblu
jofoxy,
efore going any further, have you developed any sort of an overall plan for the database? You need to determine first of all exactly what tables you will need. Then you need to analyze the relationships between each pair of table. Write your plan down. Don't try to deal with it all in your head.
Given that you have teachers and sessions, you have to determine the relationship between those two tables. Each teacher teaches many session. How many teacher can each session involve. Always only one? The relationship would be one to many. Some sessions can involve more than one teacher? Then the relationship would be many to many.
It is important to correctly define the type of relationship because there is a sinificant difference in how you implement each. A many to many requires a third (junction table).
You will also need to determine the relationship between sessions and questions, between questions and answers. Those are the significant relationships that come to mind. Let me say again, however, first determine what tables you need to store the data you need to store. Then analyze the relationship of each table to each of the other tables.
With properly structured data, writing a query (standard Access query or SQL) to get the information you want becomes almost trivial.
So I suggest that you post a list of your tables and your analysis of the relationships.
Glenn
jofoxy2000
ok this is what i got.....
EACHER TABLE SESSION TABLE QUESTION / ANSWER TABLE
surname session name session name session name
forename session date Q1 Q1
speciality session teacher Q2 Q2
Q3 A1
A1 A2
A2
A3
Tables linked by...
TEACHER surname SESSION session teacher QUESTION session name ....
I have layed out what the second row of question table will look like .... is this correct?
regards
jofoxy
jofoxy2000
ok this is what i got.....
EACHER TABLE
surname
forename
speciality
SESSION TABLE
session name
session date
session teacher
QUESTION / ANSWER TABLE
session name session name
Q1 Q1
Q2 Q2
Q3 A1
A1 A2
A2
A3
Tables linked by...
TEACHER surname SESSION session teacher QUESTION session name ....
I have layed out what the second row of question table will look like .... is this correct?
regards
jofoxy
sorry if last post came up messed up...
argeedblu
Jofoxy,
nless I am misunderstaning what you have described, you are missing a couple of important tables.
First of all, Questions and Answers should be separate tables.
Secondly, I am not clear on who is voting nor if you need the details of the votes. So you may need as session votes table, and a voters table. (Technically, voters and teachers are both people, so you would have only a single people table listing all people interacting with the sessions.)
Each table you have should have a primary key field to identify the record. So in the teacher table in addition to surnname, forename, and speciality, you would have an additional (autonumber) primary key field. In the session table you would have session name, session date, and session teacher id, as well as a primary key filed. In the session teacher id field you would store the value of the primary key for the relevant teacher (not the teacher's name!)
If you haven't already done so, I suggest you review the materials I have provided links to earlier in this thread. What I am suggesting will make a lot more sense in the context of the design and normalization articles.
Glenn
jofoxy2000
after reading over you articles. this is what i have come up with so far...note: students are voting on evaluative questions about a presentation a teacher has presented.
.g. how clear are the teaching aids..... 1-5
There are the tables i have come up with....
TEACHER
teacherID (autonumber) PK
surname
forename
speciality
SESSION
sessionID (autonumber)
session name
session date
session teacher (teacher ID)
QUESTIONS
questionsID
sessionID
session date
Q1
Q2 (ect)
ANSWERS
answersID
session ID
session date
A1
A2 (ect) to link Sql ---- answers id ----questions id-----sesssion id ----teacher id (i need to seach by teacher name though)
is this correct....
regards
jofoxy
argeedblu
Hi JoFoxy,
If you mean to have separate fields in the Questions table for each question and separate fields in the Answers table, this would not be the correct structure.
I am attaching a diagram showing the basic tables that I have been suggestion.
If you create these tables and populate the teachers and questions tables with some sample data I can put together an illustration of how to retrieve the data you want using queries.
Glenn
mike60smart
Hi JoFoxy
ounds like you want an example of a Questionnaire Database
See the attached
Mike
jofoxy2000
thats great! will do... your a star....
did not think i could have more than one FK... i give it a try. i be back with an update.
regards
jofoxy x
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.