I am attempting to build an investment portfolio tracker. I have it built in Access but am attempting to build it in Airtable due to the lack of web integration in Access. I want to be able to access the database from my phone or any device. I am struggling a little bit with manipulating the data.
I currently have multiple bases which consist of single tables of what I call raw data. They are excel exports of transactions. All of these tables are similar in nature where they have a timestamp, an amount, transaction type, and a description.
Why do I have them all as separate? Each service uses different terminology, date formatting, and also has variations on the color of the value in the export. By color, I mean positive and negative numbers. For example, one service might say $5 was withdrawn, but another will say -$5 was withdrawn. I am using basic IIf functions in Access to handle flipping the sign. By differing terminology, one service may say “withdraw” while the other says “withdrawal”. Some of these variations in terminology can be quite drastic. There are too many to use a basic IF statement formula. As of right now, I have 65 records in a mapping table and it will likely double soon as the sources continue to grow. The mapping table consists of service name, source transactions type, transaction type, and a few T/F flags for handling mathematical computations.
In Access, I created a query where I did an outer join, calling all records from the Raw Data and only those which match from the Transaction Type mapping table. Below is a clip of the mapping table. It’s a simple many to one map.
TrxTypeSource TrxType
mco_stake_reward Staking Rewards
Earnings Staking Rewards
interest_earn Staking Rewards
Withdraw Withdrawal
withdrawal Withdrawal
dividend Dividends
In airtable right now, I have 2 raw data tables in 2 separate bases. I also imported the Access Mapping Table as a separate base. I have a 4th base which is a consolidated transactions table via Sync. I have both raw data tables feeding into the consolidated table which is working fine.
*For data transformation, if the transaction type in the raw data & consolidated trx table = interest_earn, I would like it to show Staking Rewards instead. I don’t care where this is done, as long as the consolidated table shows the final transaction type. Normally, I would accomplish this by establishing a join on the fields in Access or a vlookup in Excel. I’ve tried to create a linked record field but it’s not cutting it. I don’t want to select anything, i just want it to autopopulate via a look up. I don’t want a linked record, I want a linked field! The link has nothing to do with the entire record, just the value in 1 single column. I created a linked field, clicked on it and hundreds of records popped up. Made no sense to me…
Can anyone help me with this? I’ve spent hours searching the web and reading support articles and haven’t been able to find anything that suits my needs…
Long story short:
Multiple tables of raw data
Transform/Reformat raw data by referencing maps stored in other tables
Consolidate into one single transaction register
As of right now, I have the multiple tables of raw data and have consolidated them into one single table.
FYI - I am still not sure when to use a Base or a Table. Struggling there…
