Simple query

[Guest]

Have two tables, one called ExchangeRates with Country, Month and (Exchange) Rate fields, the other called ExchangeRatesLastMonth with just Country and Rate fields. The latter table needs to be updated so that it just contains one month's values from the former table (the month being the last month with non-null values). The question is how do you use the LastMonth parameter. My SQL below (when I type in the month) produces zeros for all rates. BTW I'd like to run this from a VBA routine.

UPDATE ExchangeRatesLastMonth RIGHT JOIN ExchangeRates ON ExchangeRatesLastMonth.Country = ExchangeRates.Country
SET ExchangeRatesLastMonth.Rate = [ExchangeRates].[Rate
WHERE ((([LastMonth])=[ExchangeRates].[Month]))

TIA, Rob

 

[John Viescas]

Well, you cannot include a predicate on the "left" table of a "right" join -
that turns the Outer join into an Inner one. I think what you want to do
might look like:

UPDATE ExchangeRatesLastMonth RIGHT JOIN ExchangeRates ON
ExchangeRatesLastMonth.Country = ExchangeRates.Country
SET ExchangeRatesLastMonth.Country = ExchangeRates.Country,
ExchangeRatesLastMonth.Rate = [ExchangeRates].[Rate],
ExchangeRatesLastMonth.[LastMonth] = [ExchangeRates].[Month]
WHERE ExchangeRates.Month =
DMax("Month", "ExchangeRates", "[Country] = '" & ExchangeRates,Country &
"'")

You have to use the slow DMax function because JET will declare the stupid
thing not-updatable if you try to use a Totals subquery (SELECT Max ...).
The query should now return only the latest month for each country from
ExchangeRates. Also, if the Country is a new one, the update will actually
insert the new row in ExchangeRatesLastMonth.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Have two tables, one called ExchangeRates with Country, Month and

(Exchange) Rate fields, the other called ExchangeRatesLastMonth with just
Country and Rate fields. The latter table needs to be updated so that it
just contains one month's values from the former table (the month being the
last month with non-null values). The question is how do you use the
LastMonth parameter. My SQL below (when I type in the month) produces zeros
for all rates. BTW I'd like to run this from a VBA routine.

UPDATE ExchangeRatesLastMonth RIGHT JOIN ExchangeRates ON

ExchangeRatesLastMonth.Country = ExchangeRates.Country

SET ExchangeRatesLastMonth.Rate = [ExchangeRates].[Rate]
WHERE ((([LastMonth])=[ExchangeRates].[Month]));

TIA, Rob