Multi_Column Check Before Update

[LakeDistrict Man]

I have inherited a database and need to mark certain records within the table
to indicate the last entry,

In excel I would use offsets etc to check for certain values before updating
a field, however in Access I am a bit stuck,

I have 3 columns, lets call them A, B and C

In column A is stored a customer ref number, it is unique to the customer,
however it could be entered up to x number of times dependent on the service
used on a particular date,

In column B I have a numeric, i.e. 1 to x which runs in tandem with the
customers current line entry, i.e. 1st customer entry would have 1, 2nd
customer entry would have 2 and so on…

In column C what I want to do is insert a “Y†where the last customer record
is found,

I want to run a bit of code etc that looks at Column A and Column B, finds
the last entry for that Customer, and then updates Column C with a “Yâ€

Is this possible….

Thanks in Anticipation

 

[John Spencer]

Possible? Yes. A good idea? No.

Why do I think it is a bad idea. If you add a new record, then C is no longer
correct until you fix the old record and the new record. If you delete the
last record, then you need to find the new maximum and update C.

In Access you can always find the last B for each A using a query.

SELECT A, Max(B)
FROM TableOne
GROUP BY A

If you really have to do that you could use
UPDATE TableOne
SET C = "Y"
WHERE B =
(SELECT Max(B)
FROM TableOne as Temp
WHERE Temp.A = TableOne.A)

Better might be the following which will update C for every record
UPDATE TableOne
SET C = IIF(B=DMax("B","TableOne","A=" & A),"Y",Null)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County