Limiting multiple results in query

[JT]

I have a simple query which finds the "work date" for a specific acivity from
a table. In most cases there is only one work date, however, in some cases
there are multiple work dates. How can I limit the work dates for an
activity to only the latest date.

For example:

Activity Work Date
1 01/01/09
2 02/02/09
2 02/03/09
3 05/01/09

In the above example, I only want the query to return the 02/03/09 record
for activity 2. The MAX function won't work because it returns the max work
date for all the results (05/01/09)

 

[Duane Hookom]

How do you expect to pass the activity to the query?
How do you want to use the query?

Please provide some background regarding your requirements.

 

[JT]

Don't understand your first question. but regarding your second, I have
already created a report and am getting multiple results for the same
activity, wheras I want to limit it to one.
Would it help to send a screenshot?

 

[Duane Hookom]

You stated in your first post "I only want the query to return the 02/03/09
record
for activity 2". I was wondering why "activity 2" and not activity 1 or 3.

Maybe all you need to do is create a totals query like:
SELCT Activity, Max(WorkDate) as MaxDate
FROM [simple query]
GROUP BY Activity;

 

[JT]

Sorry for the confusion, when I stated in the original post the latest date I
should have said most recent date. That is what I want returned

So I assume I create a new query and place the below in the SQL. I rarely
work directly in the SQL. So if I follow the below it won't give me the Max
value of ALL work date query results, on the max of duplicate results?

You stated in your first post "I only want the query to return the 02/03/09
record
for activity 2". I was wondering why "activity 2" and not activity 1 or 3.

Maybe all you need to do is create a totals query like:
SELCT Activity, Max(WorkDate) as MaxDate
FROM [simple query]
GROUP BY Activity;

--
Duane Hookom
Microsoft Access MVP

Don't understand your first question. but regarding your second, I have
already created a report and am getting multiple results for the same
activity, wheras I want to limit it to one.
Would it help to send a screenshot?

 

[Duane Hookom]

Max(WorkDate) will return the most recent date. I'm not sure why you are
questioning it.

The result of the SQL I suggested should be
Activity MaxDate
1 01/01/09
2 02/03/09
3 05/01/09

If you want something different, please provide significant information
about your data, report, requirements etc.

--
Duane Hookom
Microsoft Access MVP

Sorry for the confusion, when I stated in the original post the latest date I
should have said most recent date. That is what I want returned

So I assume I create a new query and place the below in the SQL. I rarely
work directly in the SQL. So if I follow the below it won't give me the Max
value of ALL work date query results, on the max of duplicate results?

You stated in your first post "I only want the query to return the 02/03/09
record
for activity 2". I was wondering why "activity 2" and not activity 1 or 3.

Maybe all you need to do is create a totals query like:
SELCT Activity, Max(WorkDate) as MaxDate
FROM [simple query]
GROUP BY Activity;

--
Duane Hookom
Microsoft Access MVP

Don't understand your first question. but regarding your second, I have
already created a report and am getting multiple results for the same
activity, wheras I want to limit it to one.
Would it help to send a screenshot?
:

How do you expect to pass the activity to the query?
How do you want to use the query?

Please provide some background regarding your requirements.

--
Duane Hookom
Microsoft Access MVP


:

I have a simple query which finds the "work date" for a specific acivity from
a table. In most cases there is only one work date, however, in some cases
there are multiple work dates. How can I limit the work dates for an
activity to only the latest date.

For example:

Activity Work Date
1 01/01/09
2 02/02/09
2 02/03/09
3 05/01/09

In the above example, I only want the query to return the 02/03/09 record
for activity 2. The MAX function won't work because it returns the max work
date for all the results (05/01/09)

 

[JT]

Again, apparently I was not clear enough in my first post. I am not looking
for the most recent date of all query results (>150), I am looking for the
most recent date when the query retrieves two sets of results that are
identicle and differ only in the WorkDate. Please re-read my original post
below. In that example, I get 2 results for activity 2 differing only in the
fact that there are 2 different Work Date values. I only want the query to
return 1 of those dates, the latest. I know that I can get the max work date
of ALL (i.e. total) query results, but I don't want that.

Max(WorkDate) will return the most recent date. I'm not sure why you are
questioning it.

The result of the SQL I suggested should be
Activity MaxDate
1 01/01/09
2 02/03/09
3 05/01/09

If you want something different, please provide significant information
about your data, report, requirements etc.

--
Duane Hookom
Microsoft Access MVP

Sorry for the confusion, when I stated in the original post the latest date I
should have said most recent date. That is what I want returned

So I assume I create a new query and place the below in the SQL. I rarely
work directly in the SQL. So if I follow the below it won't give me the Max
value of ALL work date query results, on the max of duplicate results?

You stated in your first post "I only want the query to return the 02/03/09
record
for activity 2". I was wondering why "activity 2" and not activity 1 or 3.

Maybe all you need to do is create a totals query like:
SELCT Activity, Max(WorkDate) as MaxDate
FROM [simple query]
GROUP BY Activity;

--
Duane Hookom
Microsoft Access MVP


:

Don't understand your first question. but regarding your second, I have
already created a report and am getting multiple results for the same
activity, wheras I want to limit it to one.
Would it help to send a screenshot?
:

How do you expect to pass the activity to the query?
How do you want to use the query?

Please provide some background regarding your requirements.

--
Duane Hookom
Microsoft Access MVP


:

I have a simple query which finds the "work date" for a specific acivity from
a table. In most cases there is only one work date, however, in some cases
there are multiple work dates. How can I limit the work dates for an
activity to only the latest date.

For example:

Activity Work Date
1 01/01/09
2 02/02/09
2 02/03/09
3 05/01/09

In the above example, I only want the query to return the 02/03/09 record
for activity 2. The MAX function won't work because it returns the max work
date for all the results (05/01/09)

 

[Duane Hookom]

Are you suggesting you want to ignore any records where the Activity value is
unique? Your initial post only has Activity 2 with more than 1 record.

Your use of "two sets of results" is confusing when I think you actually
mean "two records with the same Activity value".

If I am guessing correct, you could create a totals query that groups by
Activity and counts the unique dates like the following that matches your
sample records and desired output.

SELECT [Simple Query].Activity, Max([Simple Query].[Work Date]) AS MaxDate
FROM [Simple Query]
GROUP BY [Simple Query].Activity
HAVING (((Count([Simple Query].[Work Date]))>1));

Then add this query to a query with the Simple Query and join the Activity
fields. Make this final query a totals query and display

--
Duane Hookom
Microsoft Access MVP

Again, apparently I was not clear enough in my first post. I am not looking
for the most recent date of all query results (>150), I am looking for the
most recent date when the query retrieves two sets of results that are
identicle and differ only in the WorkDate. Please re-read my original post
below. In that example, I get 2 results for activity 2 differing only in the
fact that there are 2 different Work Date values. I only want the query to
return 1 of those dates, the latest. I know that I can get the max work date
of ALL (i.e. total) query results, but I don't want that.

Max(WorkDate) will return the most recent date. I'm not sure why you are
questioning it.

The result of the SQL I suggested should be
Activity MaxDate
1 01/01/09
2 02/03/09
3 05/01/09

If you want something different, please provide significant information
about your data, report, requirements etc.

--
Duane Hookom
Microsoft Access MVP

Sorry for the confusion, when I stated in the original post the latest date I
should have said most recent date. That is what I want returned

So I assume I create a new query and place the below in the SQL. I rarely
work directly in the SQL. So if I follow the below it won't give me the Max
value of ALL work date query results, on the max of duplicate results?

:

You stated in your first post "I only want the query to return the 02/03/09
record
for activity 2". I was wondering why "activity 2" and not activity 1 or 3.

Maybe all you need to do is create a totals query like:
SELCT Activity, Max(WorkDate) as MaxDate
FROM [simple query]
GROUP BY Activity;

--
Duane Hookom
Microsoft Access MVP


:

Don't understand your first question. but regarding your second, I have
already created a report and am getting multiple results for the same
activity, wheras I want to limit it to one.
Would it help to send a screenshot?
:

How do you expect to pass the activity to the query?
How do you want to use the query?

Please provide some background regarding your requirements.

--
Duane Hookom
Microsoft Access MVP


:

I have a simple query which finds the "work date" for a specific acivity from
a table. In most cases there is only one work date, however, in some cases
there are multiple work dates. How can I limit the work dates for an
activity to only the latest date.

For example:

Activity Work Date
1 01/01/09
2 02/02/09
2 02/03/09
3 05/01/09

In the above example, I only want the query to return the 02/03/09 record
for activity 2. The MAX function won't work because it returns the max work
date for all the results (05/01/09)