Unexpected results with multiple queries

[Mrs.Meeker]

I have a form with 5 option groups. Each group has an append query written to retrieve questions pertaining to the choice made in each group.

There is a continue button on the form that runs the 5 queries. When pressed the questions go to a results table.

The problem is multiple duplicates. The underlying table that contains the original questions is written in such a way that one question may be duplicated 14 times or it may only be listed once depending on how many subjects in pertains to in each of the 5 groups.

The database is too large to send but I will attemp to send a partial and hope that it's enough.

Thanks for any help!!
Rhonda

 

[dan-cat]

I've run the code a couple of times and I can't get the QuestionId to duplicate. What do you mean by multiple duplicates?

 

[Mrs.Meeker]

If you look at tblQuestionsResults at questionID you'll see some of the duplicates. The code was run only once when I saved this file. I don't understand what is happening at all. The first row has questionID "1" then questiontypeheading "0" (the zero is not valid and should not be there!)

The next 2 rows have questionID "1" (a duplicate) then quesitontypeheading "4" (a duplicate to the second row).

On the 4th row questionID "2" questionheadingtype "0" (again that is not a valid number) sheets 3.5.3d is a duplication of the first row. (The sheet numbers also should not be duplicated)

All of this has the same survey number. TblQuestions had 965 questions. There is no combination of option buttons which would display all 965 questions, yet tblQuestionsResults shows 1762 entries and 966 questions ID's.

The tblquestionsresults contents were deleted and one query was run.

Does any of this make sense?
Thanks!
Rhonda

 

[Mrs.Meeker]

More Info

I've been looking at my append queries. I am appending to tblQuestionsResults. I have QuestionTypeHeading from tblQuestions appending to QuestionTypeHeading with the following criteria: [Forms]![frmDataSelection]![txtAbutmentTypeValue]

Each append query having a different txt***Value.

Could I have done something wrong here?

Rhonda

 

[dan-cat]

When you are running the queries a second time - are you deleting the records within tblQuestionsResults beforehand?

If you're not - that will be why you are getting duplicate entries.

I've tried getting duplicate entries with a fresh empty table to start and have not managed it. Create a delete query to clear the contents of your tblQuestionsResults table before running your append queries. A quick way of testing duplicate entries is changing the index of your QuestionId field to 'Yes No Duplicates'

 

[Mrs.Meeker]

I can't delete the records with tblQuestionsResults because the information is retained for future use. (or will be, once the checklist is built and the questions are actually answered!!)

I've also tried changing my index but then it won't store the answers more than once. If I try to made several surveys it won't store the data.

I've since changed my queries to include sheet number and project number, which has helped to eliminate some of the blank spaces.

When I run the queries separately they work fine. It is when I combine them into one step that the duplication is starting. Can I add something to my code that a 'sheet' won't be displayed more than once?

(The 'sheet' is the code for the actual question, it might be available up to 14 times for various options but need only be asked once.)

You can see it in the tblQuestions.

Here is an updated copy of my db.....hmmm, may I email this to you? I can't get it shrunk down and still display what I'm talking about.

thanks
Rhonda

 

[Mrs.Meeker]

I think I've figured out the problem. I think it's the way that I set up the tblQuestions. When all options relate to one question I duplicated the question for all options. I am currently removing those duplications and will write another query to include the questions that are now coded 1 for all options.

Learning the hard way...