Append Query - Only Appends 1 ID (Need more)!!

[robertmeyer1]

Hey,

I have 2 append queries. The first is based off of code given and works great. For the second, I followed the format of the 1st and adjusted it to meet my needs. Query 1 works great. It appends each specific clientId to the table when the command button from form 1 is selected (code below). The 2nd query works as well. But it will only append 1 employee record to the table. I thought this must be because it is only allowing for 1 clientID to be appended, but when I switch to a different client and try to append an employee, it has the same response (not allowing it). If anyone has suggestions I would appreciate them. Thanks.

Background: 2 tables are being appended to. Table1 (tblAnswers), query1 (qryappendSelect), and form 1 all work perfectly. Table 2 (tblAnswersEmployee) follows the same format as tblAnswers, except it includes a EmlpoyeeId field. Qry2 and Form 2 were based off of the respective 1, qry2 had some code alterations to append the correct fields.


Query 1

INSERT INTO tblAnswers ( ClientAutoID, QuestionNumber )
SELECT tblClient.ClientAutoID, tblQuestions.QuestionNumber
FROM tblClient, tblQuestions
WHERE (((tblClient.ClientAutoID)=[Forms]![frmClient]![ClientAutoID]) AND ((tblQuestions.QuestionNumber) Not Between 601 And 799));

Form 1

Private Sub Update_Click()
On Error GoTo Err_Update_Click
    Dim stDocName As String
    stDocName = "qryAppendSelect"
If Nz(DLookup("[ClientAutoID]", "tblAnswers", "[ClientAutoID]=" & Me.ClientAutoID), 0) = 0 Then
   DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Exit_Update_Click:
    Exit Sub
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
End Sub

Query 2

INSERT INTO tblAnswersEmployee ( ClientAutoID, EmployeeAutoID, QuestionNumber )
SELECT tblEmployee.ClientAutoID, tblEmployee.EmployeeAutoID, tblQuestions.QuestionNumber
FROM tblQuestions, tblEmployee
WHERE (((tblEmployee.EmployeeAutoID)=[Forms]![sbfEmployee1]![EmployeeAutoID]) AND ((tblQuestions.QuestionNumber) Between 601 And 605 Or (tblQuestions.QuestionNumber) Between 701 And 704)); 

Form 2

Private Sub Update_Click()
On Error GoTo Err_Update_Click
    Dim stDocName As String
    stDocName = " qryEmployeeAppendSelect"
If Nz(DLookup("[EmployeeAutoID]", "tblAnswersEmployee", "[ EmployeeAutoID]=" & Me.EmployeeAutoID), 0) = 0 Then
   DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Exit_Update_Click:
    Exit Sub
Err_Update_Click:
    MsgBox Err.Description
    Resume Exit_Update_Click
End Sub

[MMcCarthy]

Robert in Query 1 we were allowing for the fact that each client needed to correspond to all questions. The second query is different because each client won't correspond to all Employees.

As you have put ClientAutoID in tblEmployees that means each employee only has one client. This doesn't sound right - can you confirm?

Mary

[robertmeyer1]

Robert in Query 1 we were allowing for the fact that each client needed to correspond to all questions. The second query is different because each client won't correspond to all Employees.

As you have put ClientAutoID in tblEmployees that means each employee only has one client. This doesn't sound right - can you confirm?

Mary

Sorry for the confusion. The Employees discussed are the ones who work for the client. So each client (company) can have several employees. Each employee should only have 1 client (company).

I will try to look at my DB from an outside perspective when posting. I just take it for granted ya’ll all know what I’m thinking when I type which of course is not the case. So my bad. Thanks again.

[MMcCarthy]

Sorry for the delay in replying Robert. Can you post the structure of the tblEmployees and tblEmployeeAnswers tables for me to look at.

Mary

[robertmeyer1]

Sorry for the delay in replying Robert. Can you post the structure of the tblEmployees and tblEmployeeAnswers tables for me to look at.

Mary

Sure thing.

tblEmployee:

EmployeeAutoID (PK); ClientAutoID (FK) linked to tblClient 1 to many; the other fields are all general employee information (name….).

tblAnswersEmployee:

EmployeeAnswerAutoID (PK); EmployeeAutoID (FK) linked to tblEmployee 1 to many; ClientAutoID (FK) linked to tblClient 1 to many; QuestionNumber (FK) linked to tblQuestions 1 to many; EmployeeAnswer.

[MMcCarthy]

Robert,

Try this. I've moved some of the Brackets. Copy and paste it as is. Access may add some more don't worry about it. If this doesn't solve the problem email me the database after christmas and I'll have a look.

INSERT INTO tblAnswersEmployee (ClientAutoID, EmployeeAutoID, QuestionNumber )
SELECT tblEmployee.ClientAutoID, tblEmployee.EmployeeAutoID, tblQuestions.QuestionNumber
FROM tblQuestions, tblEmployee
WHERE ((tblEmployee.EmployeeAutoID = [Forms]![sbfEmployee1]![EmployeeAutoID]) 
AND ((tblQuestions.QuestionNumber Between 601 And 605) 
Or (tblQuestions.QuestionNumber Between 701 And 704))); 
 

Merry Christmas

Mary