limiting query results using VBA

[Mrs.Meeker]

I have 5 queries that are run at once. Each query works fine on it's own. The problem is that one query may pick up a question and 2 other queries may pick up the same one, resulting in duplicate questions reported. Does anyone know some code that would limit the queries to reporting the question only once per incident?

Private Sub CmdNext_Click()
On Error GoTo Err_CmdNext_Click

DoCmd.SetWarnings (0)
DoCmd.OpenQuery ("qryPackageType")
DoCmd.OpenQuery ("qryProjectType")
DoCmd.OpenQuery ("qryAbutmentType")
DoCmd.OpenQuery ("qryPierType")
DoCmd.OpenQuery ("qrySuperstrType")

Exit_CmdNext_Click:
Exit Sub

Err_CmdNext_Click:
MsgBox Err.Description
Resume Exit_CmdNext_Click

End Sub


Thanks
Rhonda

 

[Mrs.Meeker]

Hi Pat,

I'm wondering if you told someone else to turn the warnings back on after running the queries. I don't recall hearing that before...But I am becoming more and more forgetful How do I do that?

What I'm trying to do is create a checklist that is generated by selections made on a form. There are 5 option groups, each option in each group has a value, these values are assigned to the questions. The question may pertain to more that one value. For more details see the following thread. I moved my inquiry here because I think it has become a VBA issue instead of a query issue.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=62110

 

[dcx693]

Perhaps Pat is thinking of this thread: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=62124

 

[Mrs.Meeker]

Hi! Good to hear from you! I came accross the execute command just yesterday, 'I think in one of your posts'.

Thanks for the link, which lead me to the code. (I'm helpless when it comes to code, but slowly learning.) They pulled me away from Access for 6 weeks to put together a 400 page training manual (YAWN) and it doesn't help my learning curve! (The training manual had to do with contract administration, not Access!)

Anyway, I'm asuming that I would use the DAO because I don't have any AxtiveX Data object...? (Currentdb.Excute...) I don't think so anyway!! I just got 'Access Inside Out' and it's really helping to learn but I'm still feeling pretty ignorant!

I've also been reading about duplicates and came across "You can use the Distinct command to ensure that there will be no duplicates in your query." I'm not done researching but do you think this might be what I need to do?

Rhonda

 

[dcx693]

It sounded from your original post that you were opening up 5 queries. What do those queries do? Add records to a table?

 

[norman]

Hi Rhonda


I have tried running your form, I cannot get any duplicates without running it more than once.

Of interest though, your results table contains data in the 'sheets' and 'project number' fields yet these do not appear in any of the append queries.

Also the questions table contains a question type 1, but the option group values start at 2, was there a previous update query which has been deleted?

Norman

 

[Mrs.Meeker]

Yes, they are append queries. There is one tblQuestions which contain all the questions for the survey. The 5 queries append to tblQuestionsResults after the user has made selections from frmDataSelection. On frmDataSelection are 5 options groups. Each of the options have been assigned a value of 1-15. When a user selects their choices and presses the continue button the 5 append queries are run. The problem is that one question may relate to several if not all the options. The individual queries work fine, but when they are combined on tblQuestionsResults there are some duplicates from the 5 different queries.

*There is one qppend query for each of the 5 option groups.

 

[Mrs.Meeker]

Hi Norman,

Thanks for finding the error. There shouldn't be a 1 in the table and I'll track it down and correct it. Must have been a typo on my part, it was question number 626 so I'm not surprised! Gets a little blurry after a while.

Now for the other items, the sheets and project number fields. I have another query (tblqryUser) that brings in the project number and two more append queries (qryGenerateQA, qryGenerateQC) that bring in the sheets. I have everything based on the survey number which you can't see because I have been unable to send the whole hog out for you to look at. And that is also why I'm thinking that you guys aren't getting the duplicates that I'm getting.

I think I've figured out why nobody is getting any duplicates but me. I've removed so much from this example that it's not working correctly.

There is a lot more to this db that I can't present. I try to remove something to shrink it down and then something won't work.

Running it more than once would change the survey number if the db was complete, so it wouldn't really be a duplicate.

Like I said earlier, the 5 separate queries work fine, it's when they are combined by the "continue" button that the duplication begins.

 

[Mrs.Meeker]

Thanks again Norman!

I found that mistake '1' it should have been a '4'.

I really appreciate you pointing that our for me!

Rhonda

 

[norman]

Hi Rhonda

I understand your problem of not being able to post the whole database.

Just one question, are the queries which bring in the sheets and project numbers append or update ? (Your last response said append)

Regards

Norman

 

[Mrs.Meeker]

Hi Norman,

They are append queries.

 

[norman]

Hi Rhonda


I have another query (tblqryUser) that brings in the project number and two more append queries (qryGenerateQA, qryGenerateQC) that bring in the sheets.


Can you post the syntax for these queries, if they are append queries as you say, they will only add rows to the table and not input information to the existing rows.

Norman

 

[Mrs.Meeker]

Well, I thought that was how they worked! I must admit that I am having a heck of a time learning some of this stuff. I have completed one database for another department and this new one has to have the special sort for bridges etc. Some of the items in my current db has been imported from my last. (instead of rebuilding the same form, etc) I hope I didn't screw something up in doing that.


qryTblUser is a Select query

SELECT tbluser.SurveyNumber, tbluser.ProjectNumber, tbluser.Name, tbluser.Office, tbluser.Date, tbluser.survey_type
FROM tbluser;

These are the other append queries

Oh, how I see, these are for the QC/QA buttons on one of the other forms. (Which opens the frmDataSelection)...

This is qryGenerateQA

INSERT INTO tblQuestionsResults ( SurveyNumber, QuestionID, Sheets, ProjectNumber )
SELECT tbluser.SurveyNumber AS Expr1, tblQuestions.QuestionID, tblQuestions.Sheets, tbluser.ProjectNumber AS Expr3
FROM tblQuestions, tbluser
WHERE (((tbluser.SurveyNumber)=[Forms]![frmUser]![surveynumber]) AND ((tblQuestions.UserType)="b"));

This is qryGenerateQC

INSERT INTO tblQuestionsResults ( SurveyNumber, QuestionID, Sheets, ProjectNumber )
SELECT tbluser.SurveyNumber AS Expr1, tblQuestions.QuestionID, tblQuestions.Sheets, tbluser.ProjectNumber AS Expr3
FROM tblQuestions, tbluser
WHERE (((tbluser.SurveyNumber)=[Forms]![frmUser]![surveynumber]) AND ((tblQuestions.UserType)="b" Or (tblQuestions.UserType)="d"));

 

[norman]

Hi Rhonda

I think that these are causing your problem, I don't think they are needed, the Sheet information and project number shouldn't be needed in the Answers table.

I have to go now, but I will look more closely in the morning, if you like.

Norman

 

[Mrs.Meeker]

Hi Norman,

I'd appreciate that but I thought I would include a note that I sent to someone via email who is looking at the entire database.

Some of the stuff in that db is being reused from another db that I created. They will both be used for the same thing but for different departments and work types.

The reason there are two is because the first one did not need to have the data selection or any special sorting. The first one had pretty much the same questions, the only difference is whether it's 'QC' or 'QA'. QA had less questions...

But this one has to be broken up into different choices if it's a bridge, a culvert or a repair. It also has completely different questions than the first one.

So if you come across something that doesn't work or seems meaningless it may be because I imported it in from the other database and haven't gotten to it yet.

This may be a bad practice, I don't know, but it saved me allot of work.

The sheet information and project numbers weren't going to the answers table until I changed the queries and answers tables. I have changed that back to the way I had it.

 

[Mrs.Meeker]

One more thing...

Here is the code behind the next button on frmUser, this form is opened prior to frmDataSelection. (prior to frmUser, is a form where the user selects survey type QC or QA) I DO need those append queries Norman...

Private Sub cmdNextStepGen_Click()
On Error GoTo Err_cmdNextStepGen_Click
DoCmd.SetWarnings False

Dim stDocName As String
Dim stLinkCriteria As String


If survey_type = "QC" Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stDocName = "qryGenerateQC"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "qryUpdateQcdone"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "frmDataSelection"

stLinkCriteria = "[tbluser.SurveyNumber]=" & Me![SurveyNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria



Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stDocName = "qryGenerateQA"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "frmDataSelection"

stLinkCriteria = "[tbluser.SurveyNumber]=" & Me![SurveyNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


DoCmd.Close acForm, "frmuser"
DoCmd.Close acForm, "frmprojectMasterdetail"
Exit_cmdNextStepGen_Click:
Exit Sub

Err_cmdNextStepGen_Click:
MsgBox Err.Description
Resume Exit_cmdNextStepGen_Click

End Sub

 

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