[Last Call] Learn how to a build a cloud-first strategyRegister Now

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
?
413 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

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 40

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 40

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 40

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 40

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 40

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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