gigifarrow
asked on
How to make a append query append based on five criterias and the count of the data in the table?
I need to append query but it can not append based on the following criterias.
How do I right this on my Append query?
If Form ID = "A" and Form name = "B" and Form Date = "C " and Form Question count = 100 then dont append
Else append
If Form ID = "D " and Form name = "E " and Form Date = "F" and Form Question count = 12
then dont append
Else Append
If Form ID = "G " and Form name = "H" and Form Date = "I " and Form Question count = 70
then dont append
Else
Append
Helpwithappendtotal.accdb
How do I right this on my Append query?
If Form ID = "A" and Form name = "B" and Form Date = "C " and Form Question count = 100 then dont append
Else append
If Form ID = "D " and Form name = "E " and Form Date = "F" and Form Question count = 12
then dont append
Else Append
If Form ID = "G " and Form name = "H" and Form Date = "I " and Form Question count = 70
then dont append
Else
Append
Helpwithappendtotal.accdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.
What Im trying to say in this append query is
If the form has the same name, same form id, same count of the answers, and same date then do not append the record.
If this data is appeneded to the table:
Form Name = B, Form Id = C, Form Date = 11/10/13, Form Question Count = 10
and this data is already in the table
Form Name = B, Form Id = C, Form Date = 11/10/13, Form Question Count = 12
It can be appended since all the criteria does not match up.
What Im trying to say in this append query is
If the form has the same name, same form id, same count of the answers, and same date then do not append the record.
If this data is appeneded to the table:
Form Name = B, Form Id = C, Form Date = 11/10/13, Form Question Count = 10
and this data is already in the table
Form Name = B, Form Id = C, Form Date = 11/10/13, Form Question Count = 12
It can be appended since all the criteria does not match up.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the advice.
Below is a example of responses of questions one Response for a questionaire and another is for another questionaire.
I need to make sure there is no duplicates for each questionaire. When I make indexes it will only let one record of the questionaire append.
ResponseID FormID Date QuestionText
40 1 11/10/13 Did you Label Vehicle?
40 1 11/10/13 Did you open Vehicle?
40 1 11/10/13 Did you drive Vehicle?
41 2 11/20/13 How do you drive a Vehicle?
41 2 11/20/13 When did you drive a Vehicle?
Below is a example of responses of questions one Response for a questionaire and another is for another questionaire.
I need to make sure there is no duplicates for each questionaire. When I make indexes it will only let one record of the questionaire append.
ResponseID FormID Date QuestionText
40 1 11/10/13 Did you Label Vehicle?
40 1 11/10/13 Did you open Vehicle?
40 1 11/10/13 Did you drive Vehicle?
41 2 11/20/13 How do you drive a Vehicle?
41 2 11/20/13 When did you drive a Vehicle?
Which columns did you choose for your multi-column unique index? In the above example you would need ResponseID, FormID, and QuestionText to uniquely identify each row. You might also need Date if you ass the same question multiple times.
If more help needed, explain step by step what to do from opening the database, to see the issue. Explain what to do, what you see and what to expect.
ASKER
1. Append the questions that were answered
2. Before appending records check for duplicates.
If the the formId , the Response ID, serial and date inputted are the same in the table do not append the records.
2. Before appending records check for duplicates.
If the the formId , the Response ID, serial and date inputted are the same in the table do not append the records.
gigifarrow, did you try to add the unique index to the table?
ASKER
Yes, I did add a unique index . The problem is that the criteria is already shows on each question that is answered. So it considers it a duplicate.
ResponseID FormID Date QuestionText
40 1 11/10/13 Did you Label Vehicle?
40 1 11/10/13 Did you open Vehicle?
40 1 11/10/13 Did you drive Vehicle?
41 2 11/20/13 How do you drive a Vehicle?
41 2 11/20/13 When did you drive a Vehicle?
This is what Im trying to do below.
Can somebody tell me how to make this code below work correctly?
IIf(([Forms]![frmFS3Questi
ResponseID FormID Date QuestionText
40 1 11/10/13 Did you Label Vehicle?
40 1 11/10/13 Did you open Vehicle?
40 1 11/10/13 Did you drive Vehicle?
41 2 11/20/13 How do you drive a Vehicle?
41 2 11/20/13 When did you drive a Vehicle?
This is what Im trying to do below.
Can somebody tell me how to make this code below work correctly?
IIf(([Forms]
When I use that I get one record not all of the records for the questions'
I need all the records for these answers.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for taking out the time to help me.
This will not work because I have more than one record with the same criteria. The question is Different. But they all belong to one form questionaire.
Question FormId Date Serial
Is the engine Hot? A 11/13/13 243343
Is the engine Cold? A 11/13/13 243343
This will not work because I have more than one record with the same criteria. The question is Different. But they all belong to one form questionaire.
Question FormId Date Serial
Is the engine Hot? A 11/13/13 243343
Is the engine Cold? A 11/13/13 243343
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To append values to a table, you need to exclude autonumber field, otherwise remove the property as primary key.