V
Vacation's Over
I have a database designed with 14 tables.
The "main" table has 4 fields - (others are related and reasonably normalized)
StoreID
PeriodID
COAID (Accounting-Chart of Accounts ID)
Value - actual data point.
MAIN Question:
1) in addition to tracking historic data we want to use this for forecasted
data. With weekly forecasts this would either require a versionID field and
lead to bloated table over time, or some way to store versions of thistable
elsewhere and import on demand. Say report on "last week's forecast vs report
on this week's actual" I know how to delete tall records then append but it
seems slow - is there a better way??
While we're at it:
2) data is downloaded from accounting system into an Excel Speadshhet, (VBA
validation and formatting is done) then uploaded into the main Table,
currently an Excel VBA sub "translates" the accounting Store name into the
Access StoreID. (an autonumber) this requires a querry to access and a
conversion for 3 of 4 fields. Is there a better (cleaner/Faster) way. Can I
append with store name and have ID automatically apprear?
The "main" table has 4 fields - (others are related and reasonably normalized)
StoreID
PeriodID
COAID (Accounting-Chart of Accounts ID)
Value - actual data point.
MAIN Question:
1) in addition to tracking historic data we want to use this for forecasted
data. With weekly forecasts this would either require a versionID field and
lead to bloated table over time, or some way to store versions of thistable
elsewhere and import on demand. Say report on "last week's forecast vs report
on this week's actual" I know how to delete tall records then append but it
seems slow - is there a better way??
While we're at it:
2) data is downloaded from accounting system into an Excel Speadshhet, (VBA
validation and formatting is done) then uploaded into the main Table,
currently an Excel VBA sub "translates" the accounting Store name into the
Access StoreID. (an autonumber) this requires a querry to access and a
conversion for 3 of 4 fields. Is there a better (cleaner/Faster) way. Can I
append with store name and have ID automatically apprear?