beginner VBA to create 2 columns

[daisy]

Hi, I haven't written in vba in Access before so I'm not sure what to write
or what my best approach is to solve my dilemma

I am starting a db it'll have a main table with several columns of data.

Each month I will have data to import into the larger table. The problem is
that I need to be able to create 2 fields that compare from other fields. I
know it's not best to have calculated fields in your tables but for our
purposes we need it.

You have a dataset where everything is collected (currently all in Excel and
this is manually done)
The data has several fields but here is an example of an abbreviated version:
Data

Booking Qtr Mbr ID Member Event TotalPd

2008-12 1111111 ABC Initial
$500
2009-5 1111112 BCD Initial
$250

New Data coming in

Booking Qtr Mbr ID Member Event TotalPd

2010-1 1111111 ABC Renewal $1000
2011-1 1111111 ABC Renewal 2 $1500

I want to be able to append the data but create a field that compares the
Initial Event TotalPd to the Renewal (Delta) and then another column that
compares the different renewal events to each other - for example Renewal and
Renewal 2

The commonality between them is their name & ID

Thank you in advance!!!!

 

[PieterLinden via AccessMonster.com]

Hi, I haven't written in vba in Access before so I'm not sure what to write
or what my best approach is to solve my dilemma

I am starting a db it'll have a main table with several columns of data.

Each month I will have data to import into the larger table. The problem is
that I need to be able to create 2 fields that compare from other fields. I
know it's not best to have calculated fields in your tables but for our
purposes we need it.

You have a dataset where everything is collected (currently all in Excel and
this is manually done)
The data has several fields but here is an example of an abbreviated version:
Data

Booking Qtr Mbr ID Member Event TotalPd

2008-12 1111111 ABC Initial
$500
2009-5 1111112 BCD Initial
$250

New Data coming in

Booking Qtr Mbr ID Member Event TotalPd

2010-1 1111111 ABC Renewal $1000
2011-1 1111111 ABC Renewal 2 $1500

I want to be able to append the data but create a field that compares the
Initial Event TotalPd to the Renewal (Delta) and then another column that
compares the different renewal events to each other - for example Renewal and
Renewal 2

The commonality between them is their name & ID

Thank you in advance!!!!

You can link to your Excel sheet and import using a query. If not all the
data is new, you can use the Find Unmatched query wizard and then convert the
result into an append query. Definitely don't store all those extra columns.
Just use a query to get the records you want - the first would be for the
initial event and the other would be for the non-initial event you are
looking for. Then you can use correlated subqueries and other fun things
like that.

 

[daisy]

I agree it's not ideal to do this - if it ends up being okay not to add the
columns how in the query would I show the comparison for any individual mbr

The Event distinguishes the Initial vs Other?

Thanks again