Complex Select Statement Problem

[ToniS]

I have select statement that I basically would like to print a report for all
of the members that have not registered for the current show. the below
statement is really close... The poblem that I am having is if a member has
not gone to any shows they will print (which is what I want) but if a member
has gone to a different show they will not print.

I think part of the problem is the NOT EXIST (SElect * from DealersShows
Where... because the Member has gone to a previous show with the same show
category, the MemberID will be in the DealersShows table, this explains why
the ones that have gone to a prev show are not printing.. I think I will need
to include
some logic along the lines of <> to current SHowID, but I am not sure
where or how... I have played around with it for quite a while and have not
had
any luck..

Overview the MemberL table has all of the members with a Show Category 1, 2,
or 3) In this example all members with a show category of 2 and 3 have been
invited. Some members have already gone to a show with a show category of
2...

The DealersShows table holds who went to a show and which show they have
gone to...

Colmuns are
DealerShowID
ShowID (Foreign key to Shows)
MemberID (Foreign key to MemberL)
..
..

strSQL = "SELECT MemberL.Store#, ...." & _
"FROM MemberL " & _
"INNER JOIN MemberS ON MemberL.StatusID = MemberS.StatusID " & _
"INNER JOIN DirMemberL ON MemberL.MemberID = DirMemberL.MemberID
" & _
"INNER JOIN DirLocationI ON DirMemberL.LocationID =
DirLocationI.LocationID " & _
"INNER JOIN DirLocationC ON DirMemberL.LocationID =
DirLocationC.LocationID " & _
"INNER JOIN DirContactT ON DirLocationC.ContactTypeID =
DirContactT.ContactTypesID " & _
"INNER JOIN DirLocationT ON DirMemberL.LocationTypeID =
DirLocationT.LocationTypeID " & _
"WHERE (MemberL.ShowCategories = " & pubShowCategory & _
"OR MemberL.ShowCategories = 3) AND (MemberS.Inactive = 0) "
& _
"AND (DirLocationT.LocationDescription = 'Store Main') " & _
"AND (DirContactT.ContactDescription = 'President') " & _
"AND (NOT EXISTS (SELECT * FROM DealersShows WHERE
DealersShows.memberid = memberL.memberid)) " & _
"ORDER BY MemberL.Store#"



Any help would be greatly appreciated
Thanks
ToniS

 

[Sylvain Lafontaine]

You forgot to mention what's the value of current ShowID. If it's a local
VBA variable, then:

"AND (NOT EXISTS (SELECT * FROM DealersShows WHERE DealersShows.memberid =
memberL.memberid and DealersShowID <> " & CurrentShowId & ")) " & _

Also, using aliases for the name of tables will make much easier to
read/write. They might also be necessary for complex subqueries where
values from the same table will be compared between the subquery and the
parent query (for example, ranking comes to my mind).

 

[ToniS]

Thanks for the quick response... I have tried that and I get the same
resutls, the members that have gone to a show but not the current one will
not print on the report, below is how I modified my SQL statement

"AND (NOT EXISTS (SELECT * FROM DealersShows DS WHERE DS.MemberID =
MemberList.MemberID " & _
"AND DS.ShowID <> '" & pubShowID & _
"')) " & _

 

[Sylvain Lafontaine]

Sorry, my mistake, you must use an equal sign for the ShowId:

"AND (NOT EXISTS (SELECT * FROM DealersShows DS WHERE DS.MemberID =
MemberList.MemberID " & _
"AND DS.ShowID = '" & pubShowID & _
"')) " & _

Also, you are enclosing the value of pubShowID between single quotes. This
is not required if DS.ShowID is a numerical value.

 

[ToniS]

THANKS, that worked, I should have seen that, ugh! TGIF