SELECT DISTINCT

[MacDuff]

Hello
Is there a way to group the fields in a select SELECT DISTINCT statement so
that the distinct part works only with the first three fields and the
additional 4th field (event_id) and is always unique is also returned.

SELECT DISTINCT organization, tod, location, event_id FROM events

the event_id field is unique in all records, so selecting as shown causes
all records to be returned.

I need to use DISTINCT as the return data is used to populate a list which
serves as a template for new records. I do not wish duplicate entries in
the template list.

If event_id could be grouped outside the distinct organization, tod,
location part of the select statement then only those records that have
uniquely different fields would be returned. The return of the fourth
field, would enable me edit and update a returned record since I would have
a unique key value for the update command.

 

[John Vinson]

The return of the fourth
field, would enable me edit and update a returned record since I would have
a unique key value for the update command.

Well... you really wouldn't; if there are six records with the same
three fields and you returned only one EventID, you would be able to
update only one of the six records!

A Totals query, grouping by the first three fields and selecting First
of the EventID, will return an arbitrary one of the eventID's in the
group. Would that be adequate?

 

[MacDuff]

Hello John,

Many thanks for your insight. Yes, I think grouping by the first three
fields and selecting First of the event_ID, will returning an arbitrary
event_ID in the group would be adequate. A search of the knowledge base
yields examples using the Access Designer, but no programmatic examples.
Could you provide the proper syntax to query the table while grouping the 3
fields and including arbitrary event_id data as well?

I wouldn't expect many instances of multiple records with the identical
spelling errors. In any event, the user could simply rerun the update until
all previous spelling errors are corrected.

The select looks back 12 months (I didn't include the Where clause in the
select statement in my original message for brevity).
sSql = "SELECT DISTINCT organization, tod, location FROM Events" & _
" WHERE [date] > #" & dtmStartDate & "# Order By " & Fieldname

please note that the table has 5 fields, the 5th field, the primary key, is
event_id.

A typical scenario would be 1 return record per organization unless one or
more of the following conditions exist:
1. different tod (time of day) field data or different location field
data, or
2. as you suggested one or more identical spelling errors in the
organization or location fields.

Lets look at a likely event table data scenario:
Since the planning board meets monthly and the time range in the query is 12
months, one would expect 12 records returned if the unique event_id is
included in the query. Removing the event_id field from the query might
yield something like the following:

(please note the misspelled Planing Board data in the first record)
(note: the spelling error could also be as innocuous as simply two spaces
between the words Planning Board).

 

[John Vinson]

Answers inline.

Hello John,

Could you provide the proper syntax to query the table while grouping the 3
fields and including arbitrary event_id data as well?

SELECT Organization, TOD, Location, First([Event_ID]) FROM Events
WHERE [Date] >= [Enter start date:]
GROUP BY Organization, TOD, Location;

should do it.

I wouldn't expect many instances of multiple records with the identical
spelling errors. In any event, the user could simply rerun the update until
all previous spelling errors are corrected.

Unfortunately, neither a SELECT DISTINCT query nor a Totals query like
the above will allow updating (since Access cannot uniquely identify
which record you want to update). You'll need to update separately -
perhaps you could have a Form based on this query, and a Subform based
directly on the table, using Organization, TOD, and Location as a
three-field Master/Child Link Field property.

1. different tod (time of day) field data or different location field
data, or
2. as you suggested one or more identical spelling errors in the
organization or location fields.

Lets look at a likely event table data scenario:
Since the planning board meets monthly and the time range in the query is 12
months, one would expect 12 records returned if the unique event_id is
included in the query. Removing the event_id field from the query might
yield something like the following:

(please note the misspelled Planing Board data in the first record)
(note: the spelling error could also be as innocuous as simply two spaces
between the words Planning Board).
.
....
PJ Storytime, 7:30 p.m., Gleason Library
Planing Board, 7:30 p.m., Town Hall
Planning Board, 7:30 p.m., Town Hall
Planning Board, 7:30 p.m., Town Hall
Planning Board, 8 p.m., Town Hall
Planning Board, 8 p.m., Corey Auditorium
Poetry Reading, 7 p.m., Hollis Rm, Gleason Library
....

The missing 4th field data, the unique event_id (primary key field), is
needed to allow update of offending records.

The event table data is historical, so only changes to the organization
field rather than simply deleting the record should be allowed. This is not
perfect as the user could corrupt the historical data by changing Planing
Board to something like Board of Assessors. Fortunately, the users are
library staff, and to my knowledge they are one of the few organizations
left in this world with any credibility. (ahem, I'll get off my soapbox now

"Librarians are the Secret Masters of the Universe. They control
information. Don't EVER piss one off!" - Spider Robinson