Update Query ?

[confused]

Is there a way to

1) Assign events when adding in data thru an update query
for example if you append a bunch of data into a table could you then do
some kind of an update query to say if there is an initial event and no
others tag this one Renewal 1 etc.. the only real data you have to go on
would be Customer A and the total amt .. the event, renewal event and the
delta would all need to be added in


example:
Say Customer A comes in with an initial deal
then Customer A renews their deal

So, the data would look like when you're done.:

Year Customer Event Total Amt
Renewal Event Delta
2009 Customer A Initial $500
$1,000 500
2010 Customer A Renewal 1 $1000
$20,000 $19,000
2011 Customer A Renewal 2 $20,000

any ideas?

Thanks, Heather

 

[John Spencer]

Assumption:
== One event per year per customer

UPDATE SomeTable
SET Event = "Initial"
WHERE SomeTable.Year =
(SELECT Min(Year)
FROM SomeTable as Temp
WHERE Temp.Customer = SomeTable.Customer)

For Renewals if you need to number the renewals as 1, 2, 3, ...
UPDATE SomeTable
SET Event = "Renewal " & DCount("*","SomeTable","Year<" & [SomeTable].[Year])
WHERE SomeTable.Year >
(SELECT Min(Year)
FROM SomeTable as Temp
WHERE Temp.Customer = SomeTable.Customer)


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