Importing data each month

[Heather]

hi, with the help of these discussions I've been able to setup a working
table in Access where I've imported all of our historical data from Excel
into a table. Now, the trick is each month I need to be able to update this
table.

My question is, if I keep a working table, can I import to this each month
and then is there a way for me to somehow update or program criteria to move
data around?

Here's what happens
We download data from Biz Objects and each is assigned a Sold-to_Name and a
DealID/Sales Order # and there is a Contract End Dt -- the logic is:
If this name already exists with a different DealID/Sales Order # But same
Sold-to_Name and different Contract End Dt then the Total Fees should
populate the row where this exists in specific fields such as Renewed = Y and
Renewal Deal Amt? Is this possible?

 

[Klatuu]

The best practice for this situation is to create an import table in Access
that will match the data being imported. Then you can use update and append
queries to manipulate those data and append them to your production table.

 

[Heather]

is it possible when importing new data to change the color of it so that we
can quickly that it's new data that needs looked at to be checked against all
of our business rules? thanks

 

[Klatuu]

It would be possible, but I can't give you an answer not knowing how your
application is constructed.

As to viewing the data, humans are the very weakest at validing it. The
better approach would be to write your business rules into you application so
that the data is automatically corrected and only annoy the humans when the
computer isn't sure.

 

[Heather]

I'm wondering if I can highlight or color code the new data coming in ?? I
agree the less we have to touch the data the better but I'm not sure how to
automate thru access, some of it requires really looking at the prior data
with the new data

Here's an example of what happens:

Old data:
Customer A has purchased a deal so under Deal Type is shows New and then in
the future

New data:
Customer A purchases addt'l addons to that deal so in Deal Type we would
type in AddOn so the only way we would know it's an addon is if it had the
same expiration date and same DealId but they could also be renewing a deal
so that would show a different DealID and a different expiration date. And,
then to make it more complicated we have to say what Renewal it is -- so if
they've renewed once we would say Renewal if it's twice by looking at the
data we would say Renewal 2 etc...