filtering survey questions and still be updatable query

[speedyjhg]

Problem:
I need to filter the a series of questions by subcategory and associate them with the project specific answers. How do I do this and not get a non-updatable query? Questions can be in multiple subcategories and each project answers all the questions.

Background:
I work in an architecture firm and my boss asked me to take an excel spreadsheet and convert it into a database. This spreadsheet contains project information that we need to use to maintain continuity throughout our construction documents. There are several tabs which my boss uses to filter the line items so that it is relevant to the task at hand. (The code tab displays relevant questions for code officials, the drawings tab displays relevant questions for elevations, floor plans, details with some data being replicated in each section, etc.) The data on all the additional tabs are referenced back to the original. The problem is that new line items are getting added every couple months to which my boss then needs to copy the new line item onto every tab. Hence the request for a database.

Through this site, I have come to the conclusion that I have a variation of a survey database. There are line items (questions) that are answered for each project (600+). Each project will only answer a question once. The line items are filtered by category/subcategory. My boss also wants the model code section number (if applicable) by the line item for reference. And to make things even more complicated he would like to be able to do a side by side comparison to be able to copy line items of similar projects.

I had everything set up and displaying as I wanted and then I hit a wall. The query is not updatable. The form for editing data will not let me edit data. I've reduce the query to be updatable and these are the results:
subcategory - questions
questions - answers
questions - model codes
As soon as I add a third element the query is not updatable.

If what I'm attempting is not possible, please point me in a direction that might come close please.

 

[speedyjhg]

One other piece of data - I'm using Access 2000

 

[speedyjhg]

I found a workable solution - I just needed to look a little longer. It's a bit clunky, so if someone has a better idea, please point me in a different direction.

I used a delete query, then an append query (with everything but the project answers), then an update query (with project answers) to populate a temporary table for each viewing instance (the particular data to be viewed at a given time).

I then employed ghudson's better mouse trap logic to ensure that the save button is pushed to run an update query to pass the modified data back to the main table.