Try testing for Contacts.ZIP is NOT Null vice <>""
Also, you could probably speed this up by changing from a HAVING Clause to
a
WHERE Clause.
SELECT Contacts.Zip, Contacts.Title, First([Contacts.FirstName] & " " &
[Contacts.LastName]) AS Name, Contacts.Suffix, Q_Classes.EvCode,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State,
Contacts.LastName, Contacts.FirstName, Contacts.Title, Contacts.Suffix,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State
FROM Contacts INNER JOIN Q_Classes ON Contacts.PersonID =
Q_Classes.PresenterID
WHERE (((Contacts.Zip) IS NOT NULL) AND
((Q_Classes.EvCode)=[Forms]![EventForm]![Event]))
GROUP BY Contacts.Zip, Contacts.Title, Contacts.Suffix, Q_Classes.EvCode,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State,
Contacts.LastName, Contacts.FirstName, Contacts.Title, Contacts.Suffix,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State
ORDER BY Contacts.LastName, Contacts.FirstName;
If you are using the query grid, you would need to add Contacts.ZIP to the
query
a second time and change the second instance from GROUP BY to WHERE and
change
Q_Classes.PresenterID to WHERE.
You could also do this with a query like the following which uses a
sub-query to
identify the presenteriIDs that are to be matched.
SELECT Contacts.Zip, Contacts.Title
, [Contacts.FirstName] & " " & [Contacts.LastName]) AS Name
, Contacts.Suffix, Q_Classes.EvCode,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State,
Contacts.LastName, Contacts.FirstName, Contacts.Title, Contacts.Suffix,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State
FROM Contacts
WHERE Contacts.ZIP is NOT NULL
AND Contacts.PersonID IN (
SELECT PresenterID
FROM q_Classes
WHERE EvCode=[Forms]![EventForm]![Event])
In the query grid, you would build the select query on contacts table and
then
under the PersonID field you would insert in the criteria:
IN (SELECT PresenterID FROM q_Classes WHERE
EvCode=[Forms]![EventForm]![Event])
Uncheck the SHOW box for the PersonId field
Bob said:
Randy Harris said:
"Bob Richardson" <bobr at whidbey dot com> wrote in message
"Bob Richardson" <bobr at whidbey dot com> wrote in message
The query isn't a SQL query, its an access query. The two tables
(Contact,
Classes) are joined. My output gives me one record for every record
in
the
Classes file - which results in the same person showing up multiple
times
when he teaches two or more classes.
It seems that the DISTINCT feature might work, but how to implement
it.
I have to eliminate the duplicates either in the query, OR the
report
that
produces the labels.
Sounds like you need to add a DISTINCT keyword to your query.
Please
post
your query here and we'll give you some advice on modifying it.
--
-Ted
SQL is a language that is common to both SQL Server and Access.
There
are
some differences between the two implementations but there is far
more
in
common than different. The DISTINCT qualifier is common to both. As
Ted
stated earlier, it's difficult to provide much help without seeing
the
SQL
that your working with.
"Select PersonName from Person"
would return a list of names from the Person table, but it would
include
any
duplicates that existed.
"Select DISTINCT PersonName from Person"
would return a list, but each name would be listed only once.
HTH,
Randy Harris
Thanks Randy. Where would I use a SQL statement to define my data in
a
report, or in a query? I don't see a spot for that.
I tried using a TOtal query, with this definition
Name: First([Contacts.FirstName] & " " & [Contacts.LastName])
as an expression.
It almost worked. FOr some reason it also included the very first
person
in
the file, although she isn't in the selected list of classes ?
Like always, there's more than one way to do it. In most cases, you
will
create the query within the definition of the report. As you design the
report, open the parameter window and find RecordSource. When you click
on
the button for RecordSource it will open the query builder. Use the
query
builder to retrieve whatever data is required for the report.
Another method is to simply create a saved query. You can use the query
builder to confirm that you are getting the data that you want to
appear
in
the report, then make that query the RecordSource for your report.
You still haven't provided a lot of information about what you are
trying
to
accomplish. Any of the Aggregate queries (including Total) will permit
you
to "Group By" whichever field or fields you wish to. The Group By will
prevent the record from appearing more than once in the output.
Randy
Thanks Randy - I didn't know about SQL View. Here's the SQL
SELECT Contacts.Zip, Contacts.Title, First([Contacts.FirstName] & " " &
[Contacts.LastName]) AS Name, Contacts.Suffix, Q_Classes.EvCode,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State,
Contacts.LastName, Contacts.FirstName, Contacts.Title, Contacts.Suffix,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State
FROM Contacts INNER JOIN Q_Classes ON Contacts.PersonID =
Q_Classes.PresenterID
GROUP BY Contacts.Zip, Contacts.Title, Contacts.Suffix, Q_Classes.EvCode,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State,
Contacts.LastName, Contacts.FirstName, Contacts.Title, Contacts.Suffix,
Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.State
HAVING (((Contacts.Zip)<>"") AND
((Q_Classes.EvCode)=[Forms]![EventForm]![Event]))
ORDER BY Contacts.LastName, Contacts.FirstName;
This correctly gets me all the Names for the Presenters who have been
assigned to a class in the EvCode...EXCEPT it also gives me one extra
record...the first name alphabetically with a non blank zip code.
