query problem

[Grip]

I've got a table of Organizations and a table of Memberships.
Memberships have an OrgID and one Org can be part of many
Memberships. Memberships have an expiration date.

I'm trying to build a list of Organizations that have at least one non-
expired Membership associated with it. I'd like each current Org to
show once in a list (for an unbound combo box).

I tried:

SELECT tblOrganizations.OrganizationID, tblOrganizations.Name,
[tblMemberships.Anniversary]>(Date()-60) AS [Current]
FROM tblOrganizations INNER JOIN tblMemberships ON
tblOrganizations.OrganizationID = tblMemberships.OrganizationID
WHERE ((([tblMemberships.Anniversary]>(Date()-60))=True))
ORDER BY tblOrganizations.Name;

But that gives multiple instances of each organization.

Any ideas?

Thanks,
G

 

[Daryl S]

Grip -

Try this - shows only distinct (unique) rows, no duplicates:

SELECT DISTINCT tblOrganizations.OrganizationID, tblOrganizations.Name,
[tblMemberships.Anniversary]>(Date()-60) AS [Current]
FROM tblOrganizations INNER JOIN tblMemberships ON
tblOrganizations.OrganizationID = tblMemberships.OrganizationID
WHERE ((([tblMemberships.Anniversary]>(Date()-60))=True))
ORDER BY tblOrganizations.Name;

 

[John W. Vinson]

I'm still an Access newbie and I'm wondering if the DISTINCT keyword
is represented in the query Design view or it just shows up when
editing in the SQL view.

It's buried. Rightclick the background of the tables and select Properties (or
select View... Properties from the menu - not sure where it is on the 2007
ribbon). Toggle the "Unique Values" property.