Learning Access. Database design help?

[WonderWhy]

Hello,

First time poster. Please be gentle.

I'm learning Access as a hobby (maybe I can use it for work if I ever master it). I've created a small database that has become very convoluted because I think I've designed it poorly.

Here is what I have so far with attached pic:

User table with associated form > User enters info about themselves; name, address, etc.

Linked to a table and form that allows user to select areas they are interested in; Movies, Sports, Reading, etc. About 25 of these.

The next step is where it gets convoluted:

Each of these interest areas (e.g. Movies, etc.) has questions associated with them that area. So I have table with that has areas, categories, sub-categories and questions (e.g. Movies, Indy, Action, Do you like car chases?...another e.g. to compare; Movies, Studio, Action, Do you like car chases?). Since the user has selected areas that are of interest to them already, I'm displaying on a form only questions from other areas that they did not select in the previous form (via a query). Once they answer the questions (via checkboxes) I run a query that shows the questions they answered "yes" to and the area associated with it. So if a user did not select "Movies" as an area of interest but during the question they answer that "yes" they like watching car chases this form shows them that Movies is another area of interest for them.

The problems:
1 - From the example you can see that there may be a lot of redundancy in the questions and I don't want to answer the same question over and over again. I want them to answer the question once, and it answers the same question for "Movies" "Driving" "Nascar"
2 - If the user agrees that "yes" they like movies, how do I have a button automatically update the User Interests table to reflect that. I've tried using Update Queries with no success. What happens if they change their mind later on? I've created some update queries that clear out their "yes" responses from the table but seems convoluted.
3 - If the user disagrees that even though they answered the question with a "yes" should I just ignore this?

 

[wazz]

you could associate each question with any or all categories by creating a "junction table" for each interest. something like this:

tblInterests
InterestID (PK)
Movies
Sports
Literature
etc.

tblQuestions
QuestionID (PK)
Question

tblMovieQuestions
InterestID (FK)
QuestionID (FK)

tblSportsQuestions
InterestID (FK)
QuestionID (FK)

tblLitQuestions
InterestID (FK)
QuestionID (FK)

- note the two foreign keys in each junction table. in design view of each junction table, select both foreign keys (highlight both) and click/select primary key to build the primary key out of both of those fields. this will make sure you don't add the same question to the interest twice.
- hopefully you can just associate your questions within the main interest to all subcategories:

tblQuestions --> tblLitQuestions <-- tblLit <--> tblLitSubCat
tblQuestions --> tblSportsQuestions <-- tblSports <--> tblSportsSubCat

anyone with an interest in one of the literature subcategories will still view all literature questions.

 

[WonderWhy]

Wazz,

Thanks for responding...

I tried, and failed, to implement it how you suggested. So I tried a different route and got everything working. Only problem - its very clumsy and I know that Its not as "clean" as it could be.

Do you know how I can get someone to look at it and make recommendations for fixing it or do I go with the philosophy of "if it ain't broke, don't fix it"?

Thanks.

WW