Prev: Adding variables
Next: Where are Mod's defined in databases
From: confused on 18 Mar 2010 18:23

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

From: John Spencer on 18 Mar 2010 20:32
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

confused wrote:
>
> 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
>
 | 
Pages: 1
Prev: Adding variables
Next: Where are Mod's defined in databases