Need help with an append query

[cdell7up]

How can I make my appendquery put the data in the next record. Right now when I run my append query it places data at the end of the first record.



I have 3 tables: RevTbl(Reviewers table this is for the person inputting info)

Questbl: the indexed question numbers and the actual question.

AnswTbl: this is the output for the query

I have an input form: RevFrm this is designed to input person info and after the query is run, Anwsubfrm input the answers to each question.

right now when I run the query the first record is great, the I go to the next record in the RevFrm run the query and the info is appended to the first record of the AnswSubform. How can I get it to append to the AnswSubform in the next record?

I have attached the databaseChecklistDB.zip

[jzwp11]

You have your query set up to append a Cartesian Product. What that means it that it will add all of the questions for each of the reviewers in RevTbl EVERY time you run the query, so if you have 2 records in RevTbl it will add the questions for each of those reviewers. If you add a new reviewer and then run the query again, it will add the questions a second time for both reviewers 1 and 2 and then once for the new reviewer.

INSERT INTO AnswTbl ( Reviewer, Question, QuesNum )
SELECT RevTbl.Record_ID, QuesTbl.Question, QuesTbl.[Question Number]
FROM QuesTbl, RevTbl;

To be able to do this correctly you have to first associate the append query with only the record showing in the main form (i.e. the reviewer). The query might look something like this:

INSERT INTO AnswTbl ( Reviewer, Question, QuesNum )
SELECT forms!RevFrm!Record_ID, QuesTbl.Question, QuesTbl.[Question Number]
FROM QuesTbl

Notice that I took out the RevTbl and replaced the reviewer ID field with a reference to a form control.

Secondly it would be best to try to associate the execution of the append query with some event of the main form; perhaps a button? or perhaps only when a new reviewer is entered?

Thirdly, the other thing that might happen is that if you go to a record that already has the questions appended, how do you prevent a user from appending them a second time (if using a button)?


All of these things can be accomplished with some Visual Basic for Application (VBA) code tied to a form event or a button on the form.

[jzwp11]

I had a few minutes, so I added the code I mentioned in my earlier post. Your revised database is attached.

[cdell7up]

Thanks a million, this works great. Where do you go to create the code for the button. I am not familiar with adding VBA.

Can you tell me how I can get my question field in the AnwsFrm to show all the text? It is in the memo data type. right now it is now fitting all the text.

[jzwp11]

I figured it would be easier to explain with some screen shots, so I have attached a Word document showing how to get to the VBA window.

[cdell7up]

Ok now using this same input form, I want to add several other buttons, that will append much like the "add questions to reviewer button". How I would like the results to come a filtering of "QuesNum" in the answtbl. What I want is to press one of the additional buttons and it will display all the records that have a value of <=1410, in the QuesNum field.

What have done so far is copy the structure only data from the AnswTbl, and created a tbl call "CmclAnswTbl". I copied the button you created for me, and changed everything in the VBA code that says AnswTbl to CmclAnswTbl. I placed it on the RevFrm. It didn't work, what else do I need to do to this code to make it work?

Private Sub cmdAddRecords_Click()
Dim mySQL As String
'determine if there are already answers assigned to the displayed review; if so do not add them again. If not proceed with append query
If DCount("*", "CmclAnswTBL", "Reviewer=" & Me.Record_ID) = 0 Then
mySQL = "INSERT INTO CmclAnswTbl ( Reviewer, Question, QuesNum )"
mySQL = mySQL & " SELECT " & Me.Record_ID & ", QuesTbl.Question, QuesTbl.[Question Number]"
mySQL = mySQL & " FROM QuesTbl"
CurrentDb.Execute mySQL, dbFailOnError
'requery the subform to show the appended records
Me.Child39.Requery
Else
MsgBox "Questions have already been assigned to this reviewer"
End If
End Sub
Private Sub Command74_Click()
End Sub

[jzwp11]

What is the significance of the questions <=1410?

You should not have separate answer tables. The rule is that like data should be in 1 table.

[cdell7up]

The QuesNum field is a number field that has numbers from 1001 to 2501 these are series of questions, the 1001-1410 are a specific category of questions, i.e. "commericial contract questions, I will create other buttons that will call other rages of numbers, which represent other categories of contract.

So is there a way for me to extract a range of numbers from the QuesNum field, then have it populate the the Revsubform?

[jzwp11]

I would recommend creating a table to hold all possible categories of questions:

tblCategory
-pkCategoryID primary key, autonumber
-txtCategory

Then add a field to your QuesTbl

QuesTbl
-QuestionNumber
-Question
-fkCategoryID foreign key to tblCategory (must be a long integer number data type field)

You can run a series of update queries to update the fkCategoryID field for the specific ranges of questions you mentioned once you have defined the records in tblCategory.

You can then use that field to extract the questions you want.

[cdell7up]

Right now I do have a table that holds all possible questions. I need my input form to just ask the questions of the specific category. Example, commericial category the range is 1001-1410. I want the person reviewing the contract to use this input form as a checklist, and only ask questions that a commercial contract would ask. The other questions in the table are for other categories such as construction, or civil contract.

So right now my database makes the reviewer ask all questions from all categories, this is unnecessary and a waste of time, (says my supervisor), anyway I am trying to figure out an efficient way to have whoever inputs on this check

[jzwp11]

With the changes I indicated in post #9, you should be able to filter the questions according to the applicable category to achieve what you describe.

[cdell7up]

Ok I have created category table (CatTble) and the fields are Cat_ID(autonumber) and Category(text).

I added a field to my QuesTbl (Cat_ID) and made it the foreign key.

My question is how to run update queries for the (Cat_ID) in the QuesTbl?

[jzwp11]

A typical update query would look like this

UPDATE QuesTbl SET QuesTbl.CAT_ID = x
WHERE QuesTbl.[Question Number] Between y And z

Where x= the key value of the applicable category from the CatTble and y and z correspond to the lower and upper range of the question number you want for the indicated category.

So for example, if commerical is the applicable category name & it's key value in the category table is 1, your update query would look like this:


UPDATE QuesTbl SET QuesTbl.CAT_ID = 1
WHERE QuesTbl.[Question Number] Between 1001 And 1410

[cdell7up]

Can you help me understand the sql code that you wrote for my append query.

[cdell7up]

Can someone explain this process to me.