Need help with an append query

[jzwp11]

I assume you mean this code in the database I posted earlier:


Dim mySQL As String This just sets up a string variable to hold the SQL text of the query

'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("*", "AnswTBL", "Reviewer=" & Me.Record_ID) = 0 Then The IF..THEN checks to make sure that the question records have not already been added for the reviewer shown in the main form (identified in the Record_ID control on the main form). If they have not the code directly below executes. If records have already been added (i.e. the Dcount>0), then the message after the ELSE statement below will execute



The following is the actual SQL text; I just broke it down into three lines that add each successive line into the mySQL variable.
mySQL = "INSERT INTO AnswTbl ( Reviewer, Question, QuesNum )"
mySQL = mySQL & " SELECT " & Me.Record_ID & ", QuesTbl.Question, QuesTbl.[Question Number]"
mySQL = mySQL & " FROM QuesTbl"
The above query brings in the key field of the current record shown in the main form via the me.Record_ID. The "me." is just a shorthand for the current form, the SELECT..quesTbl.Question, QuesTbl.[Question Number] bring in the question information forom the QuesTbl


The following is the command that executes the query in the mySQL variable
CurrentDb.Execute mySQL, dbFailOnError
'requery the subform to show the appended records
Me.Child39.Requery This command requeries/refreshes the subform so it shows the records that were just added by the query above
Else
This is the response you will see if the IF..Then resulted in a false condition (i.e. the records have already been added
MsgBox "Questions have already been assigned to this reviewer"
End If

[cdell7up]

Now how would i go about making this update query update the records on the RevFrm and not just the QuesTbl, and how can I get it to just show the records that match the specified criteria of the update query?

[jzwp11]

Now how would i go about making this update query update the records on the RevFrm

I'm not sure what you are asking. An update query can only update records in 1 table or query. So if you want to update records in TBLRev in some way, you would have to create and run a different update query. If a form is bound to a table or query that gets updated, you should see the updated records when you open the form.

[cdell7up]

If you take a look at post #13, I created a query similar to it. Now when I run the query, the records that are updated are only in AnswTbl. I was wanting it to update the records that is in the RevFrm also. The records in the AnswTbl have been updated with the "1" indication commercial, but I still see all the other records that have a blank in that field.

[jzwp11]

The records in the AnswTbl have been updated with the "1" indication commercial, but I still see all the other records that have a blank in that field.

You will have to run other update queries: 1 for each different category.

I was wanting it to update the records that is in the RevFrm also

RevFrm does not show the questions since it is based on tblRev. Technically speaking you should not be repeating the text of the question in tblAns; it would be a violation of normalization rules. You would typically use a query to connect the two tables and then you could bring in fields from both tables including the category field.

[cdell7up]

You said "You would typically use a query to connect the two tables and then you could bring in fields from both tables including the category field."

My RevFrm does link the RevTbl and AnswTbl. I was thinking that since the append query focuses on the RevFrm, the update query should also, because it is updating AnswTbl, right or wrong?

[jzwp11]

The row source for RevFrm (based on your earlier database version) was based solely on the RevTbl. The subform on that form was based on AnswTbl. (You can verify this by opening the form in design view then going to the data tab of the property sheet and look at the Record Source property). Neither were based on a query. RevFrm and the subform are indeed linked.

[cdell7up]

Ok I get it now. So my update query is working and I understand now what is going on. So I have another challenge. Here is a scenario. Let's say Reviewer 1 enters his name and other personal information on the RevFrm, then he runs the append query to and the questions. Let's say he has commercial contract and hits the commercial button and then the 1 is added to the CategoryIn field for records that fall between 1001 and 1401. How can I get the AnswTbl subform to show "only" the records that have the 1 in the CategoryIn field?.

[jzwp11]

Let's say he has commercial contract and hits the commercial button and then the 1 is added to the CategoryIn field for records that fall between 1001 and 1401

The category field in question table only needs to be defined/populated once for each question, and once populated it probably will never need to be changed. So once you run all of the necessary update queries to populate the category for each question, that table will stay as is unless of course if you add new questions.

Relative to adding records via button on RevFrm, you would need to change the code that currently adds all questions to the answer table for the reviewer to just the questions of the appropriate category or categories. You will need to add some way of defining or selecting the appropriate category/categories to the RevFrm (i.e. a combo box or list box). By the way, did you add a category table to your database? If so, can you post a current copy of your database?

So in your business process, will you need to assign the questions for 1 category or more than one category?

[cdell7up]

Here is a copy of my current database. You were going to help me write the code to my update query. I have attached the database. Please help me figure out what to do.ChecklistDBv2.0.zip

[jzwp11]

I looked at your database. You have not categorized the other questions in QuesTbl. You will need to do that before we can test the append query.

Also, you did not address the following item I mentioned earlier; it will play a key role in how the append query pulls the questions.

You will need to add some way of defining or selecting the appropriate category/categories to the RevFrm (i.e. a combo box or list box).

When you assign questions to a reviewer, can the questions come from multiple categories or just 1?

In looking at your RevFrm, it looks like the questions & the answers are really tied to a contactor and the reviewer is your employee. Does a contractor go through this review process only once or multiple times? Does the same reviewer always conduct the review for the same contractor?

[cdell7up]

The thing about the categories of questions is each category has most of the same questions, but there is a series of questions that set them apart.

Commercial= 1001-1411
Major Systems=1001-1411 and 1501-1507
R&D = 1001-1411 and 1603-1612
Construction = 1001-1411 and a701-1718
A&E = 1001-1411 and 1801-1814
Services (Non Fss) = 1001-1411 and 1901-1910
Supplies (Non FSS) = 1001-1411 and 2001-2009
FSS = 1001-1411 and 2201-2214
FMS = = 1001-1411 and 2301-2306

How would I go about the update query with these values?

[cdell7up]

I am wondering if i should have a table that has the questions that all categories include (primary table) (1101-1310). and then a separate tables for commericial (1401-1411), Major Systems 1520-1507, R&D 1603-1612, Construction 1701-1718, A&E 1001-1062, Services (Non FSS) 1901-1910, Supplies (Non FSS) 2001-2009, FSS 2201-2219, FMS 2301-2306. Then do an append query to include the primary table and the category table i.e. R&D. The only difference in category questions are the values I named above. Do you think this is a good thing or not?

[jzwp11]

How would I go about the update query with these values?

You can't.

If a question can belong to many categories, that is a one-to-many relationship, and we already know that a category has many questions. When you have 2 one-to-many relationships between the same two entities (categories and questions in your case) you have a many-to-many relationship which requires a junction table.

CategoryQuestionsTbl
-pkCatQuesID primary key, autonumber
-fkCat_ID foreign key to CatTbl
-fkQuestionNumber foreign key to QuesTbl

This also means that we cannot have the category field in the question table as we had set up previously.

This is an example of something that has to be sorted out before working on forms.

I have created and populated the above table based on the information you provided on the categories & related question numbers.

I also modified the code behind the button on the Rev form.

I also removed the lookup fields in your RevTbl (contract type and branch) and moved the various choices to their own tables and linked back to the RevTbl accordingly. Having lookups at the table level are generally not recommended. This site explains why in more detail.

DB attached.

[cdell7up]

Hey this looks great, this is awesome. Please explain what you did to get the categories to cooperate. There is one thing thought I need to change some of the values for the categories.

Commercial= 1001-1310 and 1401-1411
Major Systems=1001-1310 and 1501-1507
R&D = 1001-1310 and 1603-1612
Construction = 1001-1310 and a701-1718
A&E = 1001-1310 and 1801-1814
Services (Non Fss) = 1001-1310 and 1901-1910
Supplies (Non FSS) = 1001-1310 and 2001-2009
FSS = 1001-1310 and 2201-2214
FMS = = 1001-1310 and 2301-2306

where would I go to changes this to reflect the above.