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
409 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 125 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 30

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 375 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 36

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 30

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 36

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 36

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 36

Assisted Solution

by:PatHartman
PatHartman earned 375 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 36

Accepted Solution

by:
PatHartman earned 375 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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