Query Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Access 2002. I have two tables - Contacts and Events. Events is a
many-to-one table joined to Contacts. If Events contains events A,B,C,D for
a particular contact, how can I eliminate that contact listing completely if
he shows D, for instance.? I've tried the normal NOT functions in many
combinations with no success, along with all three types of JOINS.
 
Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag
 
UNTESTED --
First I would create a totals query to pull a list of contacts that I do not
want.
SELECT Contacts.ContactID, Events.[Event #],
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE Events.[Event #] Not Like "*PRESENTS" AND ((Events.[Event
Date])>#7/1/2005#))
GROUP BY Contacts.ContactID;

Then build a query from the tables and the first query
the SQL.
SELECT Contacts.LastName, Contacts.FirstName, Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID,
FirstQuery
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
WHERE ((((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) AND
((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event #])="BUGSY" AND
((Events.[Event Date])>#7/1/2005#))) AND Contacts.ContactID <>
FirstQuery.ContactID
ORDER BY Contacts.LastName, Contacts.FirstName;


Why are you pulling the same field twice?
Events.[Event #], Events.[Event #]


salintag said:
Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag


KARL DEWEY said:
Post your SQL.
Click to expand...
Click to expand...
 
Thanks, Karl. I will test out your recommendation right away. It was great
to get such a prompt response to a problem that was obviously over my head.
Ill get back to you with the test results.
salintag

KARL DEWEY said:
UNTESTED --
First I would create a totals query to pull a list of contacts that I do not
want.
SELECT Contacts.ContactID, Events.[Event #],
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE Events.[Event #] Not Like "*PRESENTS" AND ((Events.[Event
Date])>#7/1/2005#))
GROUP BY Contacts.ContactID;

Then build a query from the tables and the first query
the SQL.
SELECT Contacts.LastName, Contacts.FirstName, Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID,
FirstQuery
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
WHERE ((((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) AND
((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event #])="BUGSY" AND
((Events.[Event Date])>#7/1/2005#))) AND Contacts.ContactID <>
FirstQuery.ContactID
ORDER BY Contacts.LastName, Contacts.FirstName;


Why are you pulling the same field twice?
Events.[Event #], Events.[Event #]


salintag said:
Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag


KARL DEWEY said:
Post your SQL.

:

In Access 2002. I have two tables - Contacts and Events. Events is a
many-to-one table joined to Contacts. If Events contains events A,B,C,D for
a particular contact, how can I eliminate that contact listing completely if
he shows D, for instance.? I've tried the normal NOT functions in many
combinations with no success, along with all three types of JOINS.
Click to expand...
Click to expand...
Click to expand...
 
I was pulling the same field twice in my misguided effort to solve the
problem. I really want to isolate the contacts that show NEGRI? or BUGSY.
Thanks, again
salintag

KARL DEWEY said:
UNTESTED --
First I would create a totals query to pull a list of contacts that I do not
want.
SELECT Contacts.ContactID, Events.[Event #],
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE Events.[Event #] Not Like "*PRESENTS" AND ((Events.[Event
Date])>#7/1/2005#))
GROUP BY Contacts.ContactID;

Then build a query from the tables and the first query
the SQL.
SELECT Contacts.LastName, Contacts.FirstName, Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID,
FirstQuery
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
WHERE ((((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) AND
((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event #])="BUGSY" AND
((Events.[Event Date])>#7/1/2005#))) AND Contacts.ContactID <>
FirstQuery.ContactID
ORDER BY Contacts.LastName, Contacts.FirstName;


Why are you pulling the same field twice?
Events.[Event #], Events.[Event #]


salintag said:
Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag


KARL DEWEY said:
Post your SQL.

:

In Access 2002. I have two tables - Contacts and Events. Events is a
many-to-one table joined to Contacts. If Events contains events A,B,C,D for
a particular contact, how can I eliminate that contact listing completely if
he shows D, for instance.? I've tried the normal NOT functions in many
combinations with no success, along with all three types of JOINS.
Click to expand...
Click to expand...
Click to expand...
 
Karl, I'm having a problem following the suggested SQL. I understand the
logic of the first suggestion in pulling a list of contacts that I do not
want. But could you please explain the logic of the second SQL which uses
the First Query and the Tables?
salintag

salintag said:
I was pulling the same field twice in my misguided effort to solve the
problem. I really want to isolate the contacts that show NEGRI? or BUGSY.
Thanks, again
salintag

KARL DEWEY said:
UNTESTED --
First I would create a totals query to pull a list of contacts that I do not
want.
SELECT Contacts.ContactID, Events.[Event #],
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE Events.[Event #] Not Like "*PRESENTS" AND ((Events.[Event
Date])>#7/1/2005#))
GROUP BY Contacts.ContactID;

Then build a query from the tables and the first query
the SQL.
SELECT Contacts.LastName, Contacts.FirstName, Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID,
FirstQuery
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
WHERE ((((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) AND
((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event #])="BUGSY" AND
((Events.[Event Date])>#7/1/2005#))) AND Contacts.ContactID <>
FirstQuery.ContactID
ORDER BY Contacts.LastName, Contacts.FirstName;


Why are you pulling the same field twice?
Events.[Event #], Events.[Event #]


salintag said:
Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag


:

Post your SQL.

:

In Access 2002. I have two tables - Contacts and Events. Events is a
many-to-one table joined to Contacts. If Events contains events A,B,C,D for
a particular contact, how can I eliminate that contact listing completely if
he shows D, for instance.? I've tried the normal NOT functions in many
combinations with no success, along with all three types of JOINS.
Click to expand...
Click to expand...
Click to expand...
 
Karl, I think I understand the logic now - create a first pass to identify
those you want to eliminate and then subtract those from the main base in the
second pass. I've tried to duplicate your SQL suggestions but my query still
does not eliminate the ones that should be eliminated and I don't understand
why not. Here are my latest SQLs for possible comment.

The first query, which identifies all "PRESENTS" for elimination:

SELECT Contacts.ContactID, Contacts.LastName, Contacts.FirstName,
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE (((Events.[Event #]) Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

The second query:

SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
FROM (Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID)
INNER JOIN FirstQuery ON Contacts.ContactID = FirstQuery.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date], Contacts.ContactID
HAVING (((Events.[Event #]) Like "NEGRI?" Or (Events.[Event #])="BUGSY") AND
((Events.[Event Date])>#7/1/2005#) AND
((Contacts.ContactID)<>"[FirstQuery]![ContactID]"))
ORDER BY Contacts.LastName, Contacts.FirstName;

Thanks for your help,
salintag

salintag said:
Karl, I'm having a problem following the suggested SQL. I understand the
logic of the first suggestion in pulling a list of contacts that I do not
want. But could you please explain the logic of the second SQL which uses
the First Query and the Tables?
salintag

salintag said:
I was pulling the same field twice in my misguided effort to solve the
problem. I really want to isolate the contacts that show NEGRI? or BUGSY.
Thanks, again
salintag

KARL DEWEY said:
UNTESTED --
First I would create a totals query to pull a list of contacts that I do not
want.
SELECT Contacts.ContactID, Events.[Event #],
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
WHERE Events.[Event #] Not Like "*PRESENTS" AND ((Events.[Event
Date])>#7/1/2005#))
GROUP BY Contacts.ContactID;

Then build a query from the tables and the first query
the SQL.
SELECT Contacts.LastName, Contacts.FirstName, Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID,
FirstQuery
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event Date]
WHERE ((((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) AND
((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event #])="BUGSY" AND
((Events.[Event Date])>#7/1/2005#))) AND Contacts.ContactID <>
FirstQuery.ContactID
ORDER BY Contacts.LastName, Contacts.FirstName;


Why are you pulling the same field twice?
Events.[Event #], Events.[Event #]


:

Here's the SQL.
SELECT DISTINCTROW Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #]
FROM Contacts INNER JOIN Events ON Contacts.ContactID = Events.ContactID
GROUP BY Contacts.LastName, Contacts.FirstName, Events.[Event #],
Events.[Event #], Events.[Event Date]
HAVING (((Events.[Event #]) Like "NEGRI?" And (Events.[Event #]) Not Like
"*PRESENTS") AND ((Events.[Event Date])>#7/1/2005#)) OR (((Events.[Event
#])="BUGSY" And (Events.[Event #]) Not Like "*PRESENTS") AND ((Events.[Event
Date])>#7/1/2005#))
ORDER BY Contacts.LastName, Contacts.FirstName;

What I am trying to do is eliminate anyone who has *PRESENTS in the Event #
column and retain those who only have NEGRI? or BUGSY.
Thanks, salintag


:

Post your SQL.

:

In Access 2002. I have two tables - Contacts and Events. Events is a
many-to-one table joined to Contacts. If Events contains events A,B,C,D for
a particular contact, how can I eliminate that contact listing completely if
he shows D, for instance.? I've tried the normal NOT functions in many
combinations with no success, along with all three types of JOINS.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top