Why query does not work

[syed ripon via AccessMonster.com]

Hi There:
Why I canot make a query like the below one. Why I cant use max() in where
clause?
Then how can I do the update in a single query. In my ym4tab table I have 3
fields year1,year2, month1.
In my tab_us_res table, I have data for diff months for each year.

 

[Tom Ellison]

Dear Syed:

Why? Because you're using Access Jet. It can be troublesome in this way.

I recommend you make a separate query of the subquery:

SELECT [year], MAX([month]) AS MaxMonth
FROM tab_us_res
GROUP BY [year]

Save that as a named query and JOIN it in your UPDATE query, correlating on
[year].

Tom Ellison

 

[John Spencer]

Dear Tom,
As I recall you can't use any of the aggregate functions in an update query
when using Access SQL. You have to use the VBA domain functions or you have
to build and populate a temporary table and then update from that temporary
table.

The following might work
UPDATE ym4tab
SET month1 = DMax("Month","tab_us_res","Year=" & DMax("Year","tab_us_res"))

You might be able to make that more efficient by using
Val(Right(DMax("Year & Format(Month,""mm"")","tab_us_res"),2))

Dear Syed:

Why? Because you're using Access Jet. It can be troublesome in this way.

I recommend you make a separate query of the subquery:

SELECT [year], MAX([month]) AS MaxMonth
FROM tab_us_res
GROUP BY [year]

Save that as a named query and JOIN it in your UPDATE query, correlating
on [year].

Tom Ellison

 

[Tom Ellison]

Dear John:

I'm not sure, as you have posted your response to mine. Was I mistaken?

I recommended overcoming the difficulty by creating a separate query to
return the Max(Year) for each year. If the aggregate is in a spearate
query, doesn't that overcome this difficulty?

Not that your solution isn't totally valid, and more handy (as long as the
database is Jet and not MSDE). I'm so accustomed to making all my solutions
compatible with both, so I avoid the Domain functions like the plague, and
it shows.

Tom Ellison

Dear Tom,
As I recall you can't use any of the aggregate functions in an update
query when using Access SQL. You have to use the VBA domain functions or
you have to build and populate a temporary table and then update from that
temporary table.

The following might work
UPDATE ym4tab
SET month1 = DMax("Month","tab_us_res","Year=" &
DMax("Year","tab_us_res"))

You might be able to make that more efficient by using
Val(Right(DMax("Year & Format(Month,""mm"")","tab_us_res"),2))

Dear Syed:

Why? Because you're using Access Jet. It can be troublesome in this
way.

I recommend you make a separate query of the subquery:

SELECT [year], MAX([month]) AS MaxMonth
FROM tab_us_res
GROUP BY [year]

Save that as a named query and JOIN it in your UPDATE query, correlating
on [year].

Tom Ellison

Hi There:
Why I canot make a query like the below one. Why I cant use max() in
where
clause?
Then how can I do the update in a single query. In my ym4tab table I
have 3
fields year1,year2, month1.
In my tab_us_res table, I have data for diff months for each year.
------------------------------------------------------------------------------

 

[John Spencer]

Dear Tom,
I didn't test your method, just went on memory. Now I'll have to test.

And I just did a quick and dirty test. And I got the dreaded "Operation
must use and updatable query"

Dear John:

I'm not sure, as you have posted your response to mine. Was I mistaken?

I recommended overcoming the difficulty by creating a separate query to
return the Max(Year) for each year. If the aggregate is in a spearate
query, doesn't that overcome this difficulty?

Not that your solution isn't totally valid, and more handy (as long as the
database is Jet and not MSDE). I'm so accustomed to making all my
solutions compatible with both, so I avoid the Domain functions like the
plague, and it shows.

Tom Ellison

Dear Tom,
As I recall you can't use any of the aggregate functions in an update
query when using Access SQL. You have to use the VBA domain functions or
you have to build and populate a temporary table and then update from
that temporary table.

The following might work
UPDATE ym4tab
SET month1 = DMax("Month","tab_us_res","Year=" &
DMax("Year","tab_us_res"))

You might be able to make that more efficient by using
Val(Right(DMax("Year & Format(Month,""mm"")","tab_us_res"),2))

Dear Syed:

Why? Because you're using Access Jet. It can be troublesome in this
way.

I recommend you make a separate query of the subquery:

SELECT [year], MAX([month]) AS MaxMonth
FROM tab_us_res
GROUP BY [year]

Save that as a named query and JOIN it in your UPDATE query, correlating
on [year].

Tom Ellison


Hi There:
Why I canot make a query like the below one. Why I cant use max() in
where
clause?
Then how can I do the update in a single query. In my ym4tab table I
have 3
fields year1,year2, month1.
In my tab_us_res table, I have data for diff months for each year.
------------------------------------------------------------------------------

 

[Tom Ellison]

Dear John:

Thanks! I believe I'd forgotten that. I expected this to yield to the
"separate the queries" approach. It doesn't!

Boy, am I glad I don't use Jet!

Tom Ellison

Dear Tom,
I didn't test your method, just went on memory. Now I'll have to test.

And I just did a quick and dirty test. And I got the dreaded "Operation
must use and updatable query"

Dear John:

I'm not sure, as you have posted your response to mine. Was I mistaken?

I recommended overcoming the difficulty by creating a separate query to
return the Max(Year) for each year. If the aggregate is in a spearate
query, doesn't that overcome this difficulty?

Not that your solution isn't totally valid, and more handy (as long as
the database is Jet and not MSDE). I'm so accustomed to making all my
solutions compatible with both, so I avoid the Domain functions like the
plague, and it shows.

Tom Ellison

Dear Tom,
As I recall you can't use any of the aggregate functions in an update
query when using Access SQL. You have to use the VBA domain functions
or you have to build and populate a temporary table and then update from
that temporary table.

The following might work
UPDATE ym4tab
SET month1 = DMax("Month","tab_us_res","Year=" &
DMax("Year","tab_us_res"))

You might be able to make that more efficient by using
Val(Right(DMax("Year & Format(Month,""mm"")","tab_us_res"),2))

Dear Syed:

Why? Because you're using Access Jet. It can be troublesome in this
way.

I recommend you make a separate query of the subquery:

SELECT [year], MAX([month]) AS MaxMonth
FROM tab_us_res
GROUP BY [year]

Save that as a named query and JOIN it in your UPDATE query,
correlating on [year].

Tom Ellison


Hi There:
Why I canot make a query like the below one. Why I cant use max() in
where
clause?
Then how can I do the update in a single query. In my ym4tab table I
have 3
fields year1,year2, month1.
In my tab_us_res table, I have data for diff months for each year.
------------------------------------------------------------------------------