access rookie in need of some help

[m1kegbrown]

I have two questions, and I am going mad trying to figure them out. I hope someone can at least give me some positive guidence into the resolution of at least one problem.

Let me start by saying I have a questionaire database. It collects user information such as name and phone number, tests they took, their answers to the tests they took, and contact history.

The first problem I am having is that I have successfully built the form that gives the users info (name, number etc) and I have implemented a create new method that works, and a save that works as well. The problem is I can't figure out how to add the ability to assign a user to a project and have a row in each of the correct tables be created for that person. I have to manually input into the tables right now and would like this to be automated.

The other problem I am having is that I want to be able to use a screen to input questions, a screen to input possible global answers, and a screen to link the questions to the answers it needs. I have created 6 tables one for the user, one for the question, one for the answers, and three joining them respectivly.
The problem is I want to be able to use one form that populates itself with the questions assigned to that user, then in a combo box next to the question all the assigned answers. I can't figure it out. it either gives me all the answers for all the questions in each combo box, or gives me nothing at all. What am I doing wrong.

If you can't answer this maybe a link to where I can get more info would be great. Thanks Michael

 

[liddlem]

Hi Mike
Like you, i am much of an access rookie too, so much of what i say may not be the most effective method of solving your problem. Let me give it a go anyhow.

From reading your message, I get the idea that maybe you have not yet grasped the concept of how the tables are linked and how to use queries.

I will try to explain by way of what I think your database contains. (If anyone reading this message disagrees, please put us both on the right track.)

Your USER table contains
UserID (Key field)
PhoneNo
TestTaken
QuestionAnswers
ContactHist

You talk about assigning a user to a project, so I assume that there is a PROJECT table that probably has fields like
Project (Key field)
UserID
StartDate
...
...

Question table
QuestionNumber (Key field)
Question
QuestionType (Perhaps you have different question formats like T/False, Multiple choice, fill in the blanks etc)


Answer table
QuestionNo (Key field)
AnswerNumber (Key Field)
Answer
Feedback (If you want to give the user feedback after they answer the question - Like "Well Done" or "Please try again")


Before I begin telling you how to link the data dynamically, I need to tell you that I would certainly split the USER table something like this.

UserID (Key)
Username
TelNo

ResultsTable
QNumber (Autonumber - This is the key field)
UserID
Course (See my comments regarding linking a user to questions.)
QuestionArea (Within the course, you may want to narrow down questions. I.E. If your course is Microsoft Office, The areas might be Excel, Word, Powerpoint, Access - This will help to narrow down problem areas in your reports.Maybe your questions cover different topics like Aerodynamics, Fishing and Zootlesnooting - This will help to narrow down problem areas in your reports.)
QuestionAnswer



Now for getting the data to work "dynamically"

1. Make sure that you have set the relationship between the USER and the RESULTS tables. (Tools / Relationships - make a one-to-many join on the UserID)
2. When creating the form, using the form wizard, first choose the fields from the USER table. Then choose the respective fields from the RESULTS table. (Make sure that you include the UserID from both tables) On the next screen you will see that you form automatically has a MAIN and and a SUB section - answer the prompts accordingly.

When filling in the form, you will need to choose the student's UserID from a drop down / list box. (Please check this forum for the code on how to get the form to search for records in the subform, based on a field in the main form.)

In effect, when you choose the UserID on the main form, access should return all "sub" records that are linked to the user selected. If you now add a new record to the subform, the UserID will be inserted into the record accoridngly.


On the question of linking the Questions and answers, aply the same principle. Make a one-to-many join in the relationships screen. Build a form accordingly.


To join a user to specific questions, you may have to build another table like this.

UserID
Course
QuestionArea

Now link the User to a course / set of questions via this table.

In the problem you describe, you should not need 6 tables, but rather 3 tables and 1 query.

Tables being
User
Question
Answer


Queries being a mixture of the
User / Question / Answer

I know that the exact answer is not here, but this should give you some ideas as to how to resolve your problem.

Anyone else got some tips that I can learn from. Would be great. Thanks

 

[Mile-O]

Just on from what liddlem has said - if a project can have more than one user, and a user can have more than one project then you should create a junction table to allow this many-to-many relationship.