Automatically populating a table with data from another

[copperwasher]

Hi there

I have a three table Access Database

Table1: The list of symbols and the respective market

ID	Symbol	Market
1	AUDCAD	Forex
2	AUDCHF	Forex
3	Gold	Metals
4	Silver	Metals
5		indices
6		Bitcoin
7		Commodities
8		Oil Gas
9		BlueChip

Table2: Opening the Trade

ID	Symbol	Market	Entry Date	Entry Price	Buy Sell	Current Price	Current Date	Trade Days Open	Difference	Winning Losing
1	AUDCAD	Forex	09/11/2020 00:00	1.002	Buy	1.003	20/11/2020 00:00	11	0.00	winning
2	AUDCAD	Forex	20/11/2020 00:00	1.002	Buy	1.001	28/11/2020 00:00	8	0.00	Losing
5	AUDCAD	Forex	09/11/2020 00:00	1.002	Sell	1.001	20/11/2020 00:00	11	0.00	winning
6	AUDCAD	Forex	20/11/2020 00:00	1.002	Sell	1.003	28/11/2020 00:00	8	0.00	Losing

Table3: Closed Trades

ID	Symbol	Market	Buy/Sell	Opening Date	Exit Date	Days for Trade	Start Price	Price Exit	Difference	Won/Lost
1	AUDCHF	Forex	Buy	09-Nov-20	12/11/2020 00:00	3	1.002	1.003	0.00	Won
2	AUDCHF	Forex	Buy	20-Nov-20	28/11/2020 00:00	8	1.002	1.001	0.00	Lost
4	AUDCHF	Forex	Sell	06-Nov-20	26/11/2020 00:00	20	1.002	1.001	0.00	Won
5	Silver	Forex	Sell	05-Nov-20	27/11/2020 00:00	22	1.002	1.003	0.00	Lost

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

 

[Micron]

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]

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

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]

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

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

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

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

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