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
404 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
 
LVL 34

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 34

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
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.

 

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 34

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 34

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 34

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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 views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now