Using a query vs VBA

[WynneSMI]

I have a question about how to get a query to do the work instead of using VBA. The following I can get to work using multiple Recordset queries in VBA. I have a distinct list of numbers (query), like course number (101, 102, etc.). I have another query which pulls info from anther table (an approval table, certain users can accept or deny (Y or N) a new course. This table will have the course number (101, 102, etc) listed of course multiple times, each record enter depends on userid and course number. I want to take my first list of distinct numbers and compare them to my approvals list and pick out ONLY the courses where all users have replied Y to approve it. If there is any other char other than Y, I don't want that record. Can this be done with a query or set of queries? Thanks for you help! It is greatly appreciated!!!

 

[lespaul]

So you have a course table:
CourseID, CourseDesc

and approval table
CourseID, UserID, Approved

and you want to return the Course ID where all users have approved? Does that sum it up?

This may do what you want:

SELECT COURSEID FROM COURSE WHERE COURSEID NOT IN (SELECT DISTINCT COURSEID FROM COURSE WHERE COURSEID NOT IN (SELECT COURSEID FROM APPROVAL WHERE APPROVED <> 'Y'))

this should find all the courseids that have something other than 'Y' and then excludes them from the list so all you are left with is the courses that only have 'Y'



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[muneco]

If I understand the problem correctly, you may be able to get the information you want from one table. Try something like this:

select distinct courseid from approval where courseid not in (select courseid from approval where accept = no);

 

[lespaul]

by using accept = no you may not get the same results as accept <> yes, what if there's a Q somewhere in the accepted field? By using accept <> Yes you get everything that isn't a yes. Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[muneco]

Sorry, lespaul, we must have posted at the same instant. My reply was certainly not meant to contradict you amigo.

I supposed that the accepted field was y/n or Boolean, in which case the two phrases
accept = no
and
accept <> yes
are logically equivalent.

But - in terms of readability and execution speed - if you already have the courseid in the approval table, I see no need to query the course table for the same information.

Regards...

 

[lespaul]

didn't think you were contradicting me, amigo! And yes those same time postings can be a little unnerving! But wynne wrote:

If there is any other char other than Y, I don't want that record

which indicated to me that there could be something other than Y or N

technically we're both right, it just depends on how the data is set up.

Have a GREAT weekend!!
Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[WynneSMI]

Thanks everyone! It was all a big help and most of all, it works!!! Thanks again!

 

[lespaul]

just for fun, which one did you use???
Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!