Most Current Date

[Ken]

I have a commercial real estate application that has two
tables, one with about 175 fields, the other with about 30
fields. The larger table contains a large amount of
record information and a large number of calculations, all
of which is used on a four-tabbed form.

The smaller table contains survey data. I have a query
that uses both tables. Obviously, by having this many
fields and a lot of calculations, I am teetering on the
very of MS Access's capacity. I can't even create a
public function and call it into the query without getting
the "Query too complex" error.

My goal is to have the query pick, the most recent survey
using a date field know as EntryDate. I already tried
using the aggregate Max function in the survey table,
which does give the most recent date. However, when I
link the smaller survey table with the larger table, the
large table takes on the aggregate row as well and
puts "Group By" on all 175 fields. Of course, this
creates an error message when run as it has too
many "group bys." If you change all the "Group Bys'
to "Wheres," you get an error message this way as well.

My problem is that I need all the fields in the final
query as it is the source for the information intensive
form. I know there is a MAX function in SQL, but do I
have to write the whole query in SQL to use SQL (this
would be had with over 240 fields). I didn't see the MAX
function in VBA, but I may be wrong.

So, does anybody have an idea on how to write a formula as
criteria in the DateEntry field that would produce the
most current date (aka the oldest date or max date)? Any
help or suggestions would be appreciated.

Ken

 

[Steve Schapel]

Ken,

It seems to me that you will get the desired outcome by making a simple
Select Query, based on both your tables, no Group Bys, and then in the
criteria of the EntryDate field, put either...
DMax("[EntryDate]","Surveys")
.... or...
In(SELECT Max(EntryDate) FROM Surveys)

By the way, it is almost certain that your table design could be
considerably simplified... but that's another question!

 

[Guest]

Steve,

That worked! Thanks for your quick response. I will make
note of this workaround for future use.

Yes, you are right. I am more of a modeler from the
spreadsheet days and do need lessons on relational theory
and proper database structure and normalization.

Thanks again - Ken

-----Original Message-----
Ken,

It seems to me that you will get the desired outcome by making a simple
Select Query, based on both your tables, no Group Bys, and then in the
criteria of the EntryDate field, put either...
DMax("[EntryDate]","Surveys")
.... or...
In(SELECT Max(EntryDate) FROM Surveys)

By the way, it is almost certain that your table design could be
considerably simplified... but that's another question!

--
Steve Schapel, Microsoft Access MVP

I have a commercial real estate application that has two
tables, one with about 175 fields, the other with about 30
fields. The larger table contains a large amount of
record information and a large number of calculations, all
of which is used on a four-tabbed form.

The smaller table contains survey data. I have a query
that uses both tables. Obviously, by having this many
fields and a lot of calculations, I am teetering on the
very of MS Access's capacity. I can't even create a
public function and call it into the query without getting
the "Query too complex" error.

My goal is to have the query pick, the most recent survey
using a date field know as EntryDate. I already tried
using the aggregate Max function in the survey table,
which does give the most recent date. However, when I
link the smaller survey table with the larger table, the
large table takes on the aggregate row as well and
puts "Group By" on all 175 fields. Of course, this
creates an error message when run as it has too
many "group bys." If you change all the "Group Bys'
to "Wheres," you get an error message this way as well.

My problem is that I need all the fields in the final
query as it is the source for the information intensive
form. I know there is a MAX function in SQL, but do I
have to write the whole query in SQL to use SQL (this
would be had with over 240 fields). I didn't see the MAX
function in VBA, but I may be wrong.

So, does anybody have an idea on how to write a formula as
criteria in the DateEntry field that would produce the
most current date (aka the oldest date or max date)? Any
help or suggestions would be appreciated.

Ken

.