Consistency check query

[reportbuilder]

Hi,

I'm trying to build a query to determine if primary and secondary is consistent.

I have 1 datasheet with questionnaire data. Each questionnaire has a unique id. Some questionnaires are entered double to determine the consistency of the data. The original questionnaires are entered as primary and the doubles are entered as secondary. The questionnaires have approx. 75 questions.

I wonder if it is possible to build a query to find (and count) the inconsistencies between the primairy and secondary entries.

To determine the primary data I use the query

SELECT * FROM VV_questionnaires WHERE [VV_questionnaires].version = primary

and for the secondary data

SELECT * FROM VV_questionnaires WHERE [VV_questionnaires].version = secondary

The match between the primary and secondary data is made via the following query:

SELECT [qrySecondary].*, [qryPrimary].*
FROM [qrySecondary] INNER JOIN [qryPrimary] ON [qrySecondary].code = [qryPrimary].code;

Now I cannot figure out a way to build a query to determine the differences between the two queries without building a query per question. Any suggestions?

 

[PNGBill]

How are the questions entered into your database?

I am thinking if you are able to group on question the result of a join between primary and secondary questionnaire results then do a count of records the result will be 75 or more.
75 = no differences. 76 - 1 difference etc.

If still in need of help I suggest you provide a little more detail eg field names and a few values for say 5 questions as a solution for 5 should work for 75.

 

[reportbuilder]

Hi PNGBill,

Thank you for the help. Sorry my information wasn't clear.

The data is entered in as numbers. So the question might be:
(1) Gender: Male / Female
This results in a record containing the id of the questionnaire (code) and for question 1 the value 1 or 2.

An example of the data is:

id code version q1 q2 q3 q4
1 1 primary 1 2 1 1
2 1 secondary 1 2 2 1

Both entries represent the same questionnaire (1), but the person who entered the second set of data into the database has entered a different result for question 3. This is an inconsistency.

My question is how I can find all this inconsistencies and count the total. My guess would be to count all differences between every question per primary - secondary pair. This will result in 75 queries.

I hope there is a better and faster way to do this.

Thanks,
Erik

 

[PNGBill]

VBA can run through each question and get the difference and keep doing this until no more questions left - means code will work if you change the number of questions. - Not my strength though.

Interesting that where there is an inconsistency the sum of primary and secondary is 3.

If you don't need to know which question is inconsistent, just which questionnaire, then sum of primary less sum of Secondary = zero. Any other result means an inconsistency.

I will try and show this.

 

[reportbuilder]

Hi PNGBill,

Thanks again.

I was hoping for a solution without VB, but this is an option. I will look into that.

In this case the result of the two inconsistent values is 3, but another question can be someone his or her age. A typo can have a very different result.

Unfortunately I do have to know which (or at least how many) questions in the questionnaires are different. The idea is to calculate the reliability of the results.

If I have 5 questionnaires with 1 difference in each of them and I would only count the questionnaires that would mean my reliability is 0. 100% has errors.
But if I count the number of errors the reliability is 98,7% (since there were 5 inconsistencies in 5*75 questions). So the number of differences is an important factor.

I will try to work out a VB answer.

Thanks,
Erik

 

[PNGBill]

This sql will give you the Var of Primary and Secondary questions.
Sum the Var (Result) and you have a value of the differences.

SELECT TblQuestions.CodeNum, TblQuestions.Question1, Var(TblQuestions.Question2) AS VarOfQuestion2, Var(TblQuestions.Question3) AS VarOfQuestion3, Var(TblQuestions.Question4) AS VarOfQuestion4, Var(TblQuestions.Question5) AS VarOfQuestion5
FROM TblQuestions
GROUP BY TblQuestions.CodeNum, TblQuestions.Question1;


Each difference is 0.5. therefore, 5 diff's = 2.5

This may mean you run two queries. First will get the Var and the second will run the first and sum the result.

 

[reportbuilder]

Hi PNGBill,

This might just work. I'm going to try that.

Thanks for the help.
Erik

 

[PNGBill]

Your age question throws out the var sum idea as you no longer have zero or 0.5 as result.

 

[PNGBill]

correct sql

SELECT TblQuestions.CodeNum, Var(TblQuestions.Question1) AS VarOfQuestion1, Var(TblQuestions.Question2) AS VarOfQuestion2, Var(TblQuestions.Question3) AS VarOfQuestion3, Var(TblQuestions.Question4) AS VarOfQuestion4, Var(TblQuestions.Question5) AS VarOfQuestion5
FROM TblQuestions
GROUP BY TblQuestions.CodeNum;

but won't work for ages.

 

[reportbuilder]

Hi PNGBill,

Thanks anyway.

I'm now calculating the differences in a hidden field and know the number of differences per pair. This is already a step in the right direction. I'm now going to calculate total of those values and use the total number of entered questionnaires for the reliability calculations.

Greetings,
Erik