UPDATE, GROUP BY, MAX question?

[triplee23]

Hi,

I have an UPDATE query I hope to get some help with.


I have two tables, tblcustomers and tblreadings. The are related through on-to-many (tblcustomers -> tblreadings).

I would like the UPDATE to perform the following:

For every customer in tblcustomer, find the MAX(Reading) and corresponding ReadingDate, and add that to the MaxReading and MaxReadingDate field for that specific customer. I guess I need som GROUP BY and MAX on the tblreadings and the UPDATE, SET and JOIN that subquery to the tblcustomers, but I am unsure?


tblcustomers (simplyfied)


CustomerID - MaxReading - MaxReadingDate
------------------------------------------
1 - Null - Null
2 - Null - Null


tblreadings (simplyfied)


ReadingID - CustomerID - Reading - ReadingDate
-----------------------------------------------
1 - 1 - 200 - 2012-01-01
2 - 2 - 300 - 2012-02-02
3 - 1 - 400 - 2011-09-01

I have below given a short example of what I would like the result UPDATE to be:

tblcustomers

CustomerID - MaxReading - MaxReadingDate
------------------------------------------
1 - 400 - 2011-09-01
2 - 300 - 2012-02-02

Someone with an idea?

 

[Brianwarnock]

You are nearly correct and yet wrong at the same time!

You are wrong to be doing this as you will be carrying duplicate data, which can get out of synch on your database, this is against good db design.

You can and should get this data when required for say a report.

To get the data is a two stage affair , as you say use Group and Max on the table tblreadings, but then join this query back to tblreadings on customerid and maxofreading to reading to enable retrieving of the date.

Brian

 

[triplee23]

Thanks and I fully understand that this is not the optimal design as, as you say, I risk duplicate data that is not in sync. I will broaden the picture for you to understand the setting, maybe you have another way to solve my problem, other that the UPDATE query I suggested above.

Every now and then I need to enter new Reading(s) in the tblReadings for every CustomerID in tblCustomers. I have created a continuous form based on the relationshiop tblCustomers -> tblReadings. I would like to validate the user entry so that the the value is ALWAYS > the previous Max(Reading). The form has these fields from the tables above:

CustomerID, ReadingID, TempReading, TempDate. I would also like to add PreviousReading(Max) and the corresponsing date. I have done as you suggest, creating a query as a two step. Finding the Max(Reading) and then joining back to tblCustomers. The problem is that if I use this as source for my form, I am not able to add data to TempReading etc as the query is not updateable. I hope you understand.

I would like the MaxReading and the Max ReadingDate represented on the form and that I still am able to enter data to the fields.

Can I have fields in a form that are based on a completely different query that the other fields, I don't seem to get that to work.

Thanks!

 

[Brianwarnock]

I have not done this but would take a look at Dmax function

Brian