Limit query to one result

[Stickman64]

Hi and first let me say I am a new user so thanks in advance for your
patience and help.

I am trying to run a query that will list the next available meeting for
each project. So I am using the >Now() in the criteria box. I want just one
entry per project. I would also like it if a project doesn't have anything
scheduled, it would pull that up and put TBD in the meeting date.

What I end up getting is all future meetings for each program, with multiple
entries and can't figure out how to force it to output just the next
instance. Plus, if a project doesn't have a date for their next event, it
doesn't show at all.

Any help would be appreciated.

 

[Bob Barrows [MVP]]

Hi and first let me say I am a new user so thanks in advance for your
patience and help.

I am trying to run a query that will list the next available meeting
for each project. So I am using the >Now() in the criteria box. I
want just one entry per project. I would also like it if a project
doesn't have anything scheduled, it would pull that up and put TBD in
the meeting date.

What I end up getting is all future meetings for each program, with
multiple entries and can't figure out how to force it to output just
the next instance. Plus, if a project doesn't have a date for their
next event, it doesn't show at all.

Any help would be appreciated.

Please help us out with a description of your table structure. Also, it
never hurts to provide a few rows of sample data (relevant fields only,
and just enough rows to illustrate the problem) along with the desired
results given the sample data.

 

[John Spencer]

SELECT Program, Min(MeetingDate) as nextMeeting
FROM YourMeetingTable
WHERE MeetingDate > Now()

Save that query and now open your programs table and join that to the
saved query.

SELECT Programs.Program, NextMeeting
FROM Programs LEFT JOIN qSavedQuery
ON Programs.Program = qSavedQuery.Program




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Stickman64]

So I assume you have to do this in SQL, not in the query wizard.

I actually do have reference books for Access and am using the forums as my
last resort.

Can you make access compare the previous query and highlight only changed
text? In other words, say the meeting has passed and the next meeting is
changed. Management wants a highlighted color to draw attention to the
change. Can it do that?

Also, if no meeting is scheduled, can it automatically put "TBD" or
something like that in for that date?

Thanks again for your help.

 

[John Spencer]

You can use NZ and format functions to return TBD


SELECT Programs.Program
,IIF(NextMeeting is Null,"TBD",Format(NextMeeting,"mmm dd, yyyy") as TheDate
FROM Programs LEFT JOIN qSavedQuery
ON Programs.Program = qSavedQuery.Program



Unless you have a way in the database of identifying a changed meeting
date, I know of no good way to do what you want - heck, I don't even
know of a bad way to do it.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Stickman64]

Thanks John. You have been an immense help.

I probably wasn't very clear about the notification. What I need to do is
somehow compare the new query to say the query I ran last week. Then I would
like it to automatically highlight changes.

 

[Stickman64]

Thanks for your help. I decided to start smaller and work with bite-sized
chunks. So I formed a few fake programs, Alpha, Beta, Gamma and made up
meeting dates. When I run your suggested query, I get the next meeting date
just like I wanted! A small victory for you I am sure but a large one for
me. However, I just get the date and can't figure out how to get the related
meeting data from the same line, like Meeting Title, Chair, etc. I thought
it would be easy but ...

I get "You tried to execute a query that does not include the specified
expression 'Mtg' as part of an aggregate function" if I try to add any of the
other rows (records?) from that table.

 

[John Spencer]

Since you did not post the query you are using, it is very difficult to
give you specifics.

Normally it would be as simple as adding the other fields to the query,
something like the following.

SELECT Programs.Program
, Programs.[Meeting Title]
, Programs.[Chair]
, Programs.[Etc.]
,IIF(NextMeeting is Null,"TBD",Format(NextMeeting,"mmm dd, yyyy") as TheDate
FROM Programs LEFT JOIN qSavedQuery
ON Programs.Program = qSavedQuery.Program


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================