Update field based on aggregate fn on another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

This should be easy but I'm not having much success.

I have a table with the following fields
ServiceID
CustomerID
ServiceDate
ServiceType (Values are either: NEW, New Family Member or Return/Follow up)

I want to set the service type to New for the record having the earliest
date for each customer, and to Return/followup for all other dates.

I'm trying to use the query design grid and using the min function in the
totals row of ServiceDate but this will only identify the first service if I
don't add either the ServiceType or ServiceID fields to the grid.

If I could identify the ServiceID of the first service for each customer,
then I could use that query in another query to update the ServiceType by
joining on ServiceID.

I'm sorry for the simplicity of this request. I'm sure it is dead easy
using SQL. Any help would be much appreciated.

Anna
 
Use the AfterUpdate event of the CustomerID and the ServiceDate to DLookup()
the table and see if there is an existing record for the customer.

Would this give you the wrong results if a data entry person entered a
record for a customer, and then later entered an earlier service record? If
so, it would probably be better not to store this value. Instead, just
display the information as a calculated field.
 
Thanks Allen

Yes What you say is true and I had been working on Afterupdate and DLookup
functions but found it very difficult to cover all of the scenarios. Also,
the database has a lot of records already with inconsistent data, so if I
introduce AfterUpdate code that doesn't cover all of the scenarios of
inconsistent data, then it will make fixing bad data a nightmare for users.
Incidentally the Afterupdate code I've written worsk fine for new records.

I also agree with you that the field should be calculated and not stored,
since it is better not to give users the opportunity to store bad data.. I
have only 1 problem with this and that is in calculating this information
where the initial service is on the same date as for another person in the
family or there are 2 services on the same date for the same person.

Do you have any thoughts on this? Also, is there a way to return the
ServiceID for the earliest ServiceDate for a particular CustomerID?

thank you so much for your time

Anna
 
So the field will be a calculated one, which removes all the maintenance
problems. Good choice.

Your original post referred to a table that had a ServiceID field and a
CustomerID field. It should be possible to use the combination of ServiceID
+ CustomerID to return multiple rows for a customer who needs multiple
services. If you group by ServiceID and by CustomerID, you should get 2 rows
for the same customer if that's what you want.

Regarding the question of how to get the ID, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The article discusses 4 ways to do this.

HTH
 
Thank you so much.

Allen Browne said:
So the field will be a calculated one, which removes all the maintenance
problems. Good choice.

Your original post referred to a table that had a ServiceID field and a
CustomerID field. It should be possible to use the combination of ServiceID
+ CustomerID to return multiple rows for a customer who needs multiple
services. If you group by ServiceID and by CustomerID, you should get 2 rows
for the same customer if that's what you want.

Regarding the question of how to get the ID, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
The article discusses 4 ways to do this.

HTH
Click to expand...
 
Back
Top