Automatically populating a table with data from another

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hi there

I have a three table Access Database

Table1: The list of symbols and the respective market
IDSymbolMarket
1AUDCADForex
2AUDCHFForex
3GoldMetals
4SilverMetals
5indices
6Bitcoin
7Commodities
8Oil Gas
9BlueChip


Table2: Opening the Trade
IDSymbolMarketEntry DateEntry PriceBuy SellCurrent PriceCurrent DateTrade Days OpenDifferenceWinning Losing
1AUDCADForex09/11/2020 00:001.002Buy1.00320/11/2020 00:00110.00winning
2AUDCADForex20/11/2020 00:001.002Buy1.00128/11/2020 00:0080.00Losing
5AUDCADForex09/11/2020 00:001.002Sell1.00120/11/2020 00:00110.00winning
6AUDCADForex20/11/2020 00:001.002Sell1.00328/11/2020 00:0080.00Losing

Table3: Closed Trades
IDSymbolMarketBuy/SellOpening DateExit DateDays for TradeStart PricePrice ExitDifferenceWon/Lost
1AUDCHFForexBuy09-Nov-2012/11/2020 00:0031.0021.0030.00Won
2AUDCHFForexBuy20-Nov-2028/11/2020 00:0081.0021.0010.00Lost
4AUDCHFForexSell06-Nov-2026/11/2020 00:00201.0021.0010.00Won
5SilverForexSell05-Nov-2027/11/2020 00:00221.0021.0030.00Lost

What I am looking to do (if possible) is use the input data from Table 1: Symbol, Market, Entry Date, Entry Price, Buy Sell, etc and have this data populate the Closed Trades Table (when the time comes to closing the trade) rather than having to type it in again.

Is this possible?

I have a little bit of experience with Access, I am a keen learner, so any help would be much appreciated

CW
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,047
Office Version
  1. 365
Platform
  1. Windows
Sounds like you need to research db normalization because you're suffering from Excel brain ;)
You seem to be wanting to store repeated data, and in a big way. No real idea of what this process is about, but why would you not just have a field that contains the status of a trade, thus listing the details only once?
 

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Sounds like you need to research db normalization because you're suffering from Excel brain ;)
You seem to be wanting to store repeated data, and in a big way. No real idea of what this process is about, but why would you not just have a field that contains the status of a trade, thus listing the details only once?
Hey there Micron.

Thanks for replying
Yep, Excel familiar with, Access a lot to learn :rolleyes:

Can you elaborate on the field that contains the status of a trade?
The trade is either 1. open and current or 2. closed, hence the two tables.

Is there a way of using a unique code when opening the trade so its data can be reproduced in the 'closed trades' table by calling up the unique code?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,047
Office Version
  1. 365
Platform
  1. Windows
Your question on reproduction of data in a "closed trades" table is problematic for me. I leave all my investments in the hands of my entrusted financial advisor, so I know little about investing, nor do I want to, so you'll have to forgive my ignorance. All I can say in such situations is that understanding the entity/attribute relationship is paramount to creating a successful db schema. That is why db normalization is KEY.

If a trade has a status, that trade table could have a field that has a status value; e.g. "open" or "current". A query can get records of all trades that are either or both. If it is possible to have neither, a query can return those records as well, based on them being Null (they have no data value). IF you want to have a 'status' table, which might make sense if you had several potential statuses, then that is a lookup table. Do not confuse that with a lookup field. The primary key value of such a status table then becomes the value that is stored in the trades table, instead of the words open or current. That might be getting too deep for you at this point, and if there are only 2 possible statuses, then I probably would not bother with a lookup table. The key message here is to realize that Excel is a knife and Access is scissors. They both cut things, but you would not use scissors to carve a roast, would you? I cannot over stress the importance of normalization. Once you think you understand it, you should plot your tables schema on paper and see if you can come up with something that might work. After that point, build and test before committing to the first draft. Perhaps these will help you along the way:

Normalization Parts I, II, III, IV, and V
and/or

Entity-Relationship Diagramming: Part I, II, III and IV

How do I Create an Application in Microsoft Access?

Important for success:
Naming conventions - General: Commonly used naming conventions
MS Access Naming Conventions

What not to use in names - Microsoft Access tips: Problem names and reserved words in Access
About Auto Numbers
- UtterAccess.com
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,071
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top