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
402 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:gigifarrow
Comment Utility
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
Comment Utility
gigifarrow, did you try to add the unique index to the table?
0
 

Author Comment

by:gigifarrow
Comment Utility
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
Comment Utility
WHICH FIELDS DID YOU INCLUDE?
Please post a picture of the index dialog.
0
 

Author Comment

by:gigifarrow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

10 Experts available now in Live!

Get 1:1 Help Now