[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to make a append query append based on five criterias and the count of the data in the table?

Posted on 2013-11-20
15
Medium Priority
?
414 Views
Last Modified: 2014-03-19
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
0
Comment
Question by:gigifarrow
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 39663790
try this query format

INSERT INTO tblExampleHistory ( ID, FormId, FormName, FormDate, FormQuestion )
SELECT tblExample.ID, tblExample.FormId, tblExample.FormName, tblExample.FormDate, tblExample.FormQuestion
FROM tblExample
GROUP BY tblExample.ID, tblExample.FormId, tblExample.FormName, tblExample.FormDate, tblExample.FormQuestion
HAVING (((tblExample.FormId) Not In ("A","D","G")) AND ((tblExample.FormName) Not In ("B","E","H")) AND ((tblExample.FormDate) Not In (#1/1/2013#,#1/11/2013#,#1/21/2013#)) AND ((Count(tblExample.FormQuestion)) Not In (100,12,70)));
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39664008
Can't see the values in tables, or just are pseudo values in respective fields?

To append values to a table, you need to exclude autonumber field, otherwise remove the property as primary key.
0
 

Author Comment

by:gigifarrow
ID: 39664033
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 41

Assisted Solution

by:PatHartman
PatHartman earned 1500 total points
ID: 39664265
The simplest solution is to make a compound, unique index.

1. Open the table in design view and then the indexes dialog.
2. On the first empty line, add a unique name for your index.
3. In the next column add the first field of the index.
4. In the properties below, mark the index as unique.
5. On the next line of the indexes dialog, skip over the name field and add the second field name.
6. On the next line of the indexes dialog, skip over the name ....

You can include up to 10 columns in a primary key or index (SQL Server et. al. allow more).  As long as you leave the index name blank, Access knows you are including the new line in the previous index.
0
 

Author Comment

by:gigifarrow
ID: 39665787
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?
0
 
LVL 41

Expert Comment

by:PatHartman
ID: 39665898
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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39666495
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.
0
 

Author Comment

by:gigifarrow
ID: 39666686
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.
0
 
LVL 41

Expert Comment

by:PatHartman
ID: 39666807
gigifarrow, did you try to add the unique index to the table?
0
 

Author Comment

by:gigifarrow
ID: 39666982
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]![frmFS3Questionaire]![Serial]=[Serial]) And ([Forms]![frmFS3Questionaire]![QuestionID]) And ([Forms]![frmFS3Questionaire]![FormID]="FormID") And ([Forms]![frmFS3Questionaire]![TimeInduction]="TimeInduction"),MsgBox("This is a Duplicate"),Dcmd "qryAppendResponses")
0
 
LVL 41

Expert Comment

by:PatHartman
ID: 39667077
WHICH FIELDS DID YOU INCLUDE?
Please post a picture of the index dialog.
0
 

Author Comment

by:gigifarrow
ID: 39667279
example

When I use that I get one record not all of the records for the questions'
Example2


I need all the records for these answers.

Example3
0
 
LVL 41

Assisted Solution

by:PatHartman
PatHartman earned 1500 total points
ID: 39667551
Please re-read my instructions for building a multi-column index.  Notice that ___Uniqueindex has two columns and caremgr has two columns.  Each of the others has only one column.  The uniqueindex is actually defined as the primary key for the table which is why keys show to the left but the caremgr index is also unique and it is not the PK.  You can have up to 10 columns in a multi-column PK or index.

So, the multi-column index says that the combination of the fields must be unique.  

Multi-column index
0
 

Author Comment

by:gigifarrow
ID: 39669063
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
0
 
LVL 41

Accepted Solution

by:
PatHartman earned 1500 total points
ID: 39669140
Then you need to include a fourth field in the index.  

PS, you should also add an autonumber PK to the table if it has "children".  If it doesn't, you can use the unique index as the PK.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question