Most recent date

[Guest]

I have a query that looks at a table that contains real estate survey data. The survey is picked out depending on input from the user. Some of the surveys in the table are quarterly and some at irregular intervals. In some instances, for instance, if particular criteria is chosen, the query chooses from the irregular surveys. However, I need it to choose the most recent of the choices. I have a field known as "EntryDte." Could I use something like the Max function? The problem is that all the other criteria would have to be run first and this would be the last criteria to be considered. In my QBE I would put the other criteria first wouldn't I?

 

[Allen Browne]

A Totals query (Total icon on toolbar) could GROUP BY the pick the MAX
EntryDte if you just need the date.

If you need other fields from that record as well, see:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that looks at a table that contains real estate survey

data. The survey is picked out depending on input from the user. Some of
the surveys in the table are quarterly and some at irregular intervals. In
some instances, for instance, if particular criteria is chosen, the query
chooses from the irregular surveys. However, I need it to choose the most
recent of the choices. I have a field known as "EntryDte." Could I use
something like the Max function? The problem is that all the other criteria
would have to be run first and this would be the last criteria to be
considered. In my QBE I would put the other criteria first wouldn't I?

 

[Guest]

Allen

Thanks for the info. First, let me explain that the query has over 200 columns. It is the control source for a multi-tabbed form that provides a great deal of information and calculations. When I made the query a Totals query, I changed all the other columns (over 200) to a "Where" Status, as I couldn't Group By on over 200 columns. When I ran the resulting query, it did provide the most recent date as it was asked to do. However, it didn't show any of the "Where" fields as the Totals query unchecks the "Show" status. I checked a few of them to see if I could get them to show. I got an error message saying it can show "Where" columns

I decided to see if not showing the data in the query would affect my form that was using the query as a control source. Apparently, the fields need to be in the "Show" status for the form to be able to show it on the form. So, that solution didn't work

Next, I tried the Cascading Query suggestion. I pulled both the new query (Totals Query) and the old (existing query) into a new select query and linked the EntryDate column with the MaxEntryDate column, which was the only Column displayed on the Totals query. I hit execute, it just sat there. It didn't display the results or give me an error message. Maybe it's still calculating and I end up with some catresian product, I don't know as of yet

Anyway, any other suggestions, now that you have this new information? I prefer not to go the VBA route writing a VBA query with over 200 fileds if I can get around it. Plus, I am a neophyte VBA coder. Thanks for all your help!

 

[Allen Browne]

Okay, we are now trying to make the query efficient enough to run.

A stacked query does sound appropriate.
Just the minimum fields needed to select the most recent value.
Then another query based on that.

If you have already have the most recent record selected in the lower level
query, do you still need the aggregation in the next query? Avoiding GROUP
BY would seem to avoid the problem.

It is possible to have a field 2ce in a query: once with GROUP BY (so it
shows on your form), and the 2nd time with WHERE so the correct records are
displayed. This difference is important: Criteria under GROUP BY are
applied *after* aggregation (a HAVING clause), whereas criteria under WHERE
are applied *before* aggregation (a WHERE clause). If you have 200 fields,
you may not be able to duplicate them all in the query design grid.

Not sure that this solves your issue, but hopefully gives useful pointers.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info. First, let me explain that the query has over 200

columns. It is the control source for a multi-tabbed form that provides a
great deal of information and calculations. When I made the query a Totals
query, I changed all the other columns (over 200) to a "Where" Status, as I
couldn't Group By on over 200 columns. When I ran the resulting query, it
did provide the most recent date as it was asked to do. However, it didn't
show any of the "Where" fields as the Totals query unchecks the "Show"
status. I checked a few of them to see if I could get them to show. I got
an error message saying it can show "Where" columns.

I decided to see if not showing the data in the query would affect my form

that was using the query as a control source. Apparently, the fields need
to be in the "Show" status for the form to be able to show it on the form.
So, that solution didn't work.

Next, I tried the Cascading Query suggestion. I pulled both the new query

(Totals Query) and the old (existing query) into a new select query and
linked the EntryDate column with the MaxEntryDate column, which was the only
Column displayed on the Totals query. I hit execute, it just sat there. It
didn't display the results or give me an error message. Maybe it's still
calculating and I end up with some catresian product, I don't know as of
yet.

Anyway, any other suggestions, now that you have this new information? I

prefer not to go the VBA route writing a VBA query with over 200 fileds if I
can get around it. Plus, I am a neophyte VBA coder. Thanks for all your
help!