Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    Trying To Construct Best Practice Tables For Joining In A Form.

    Hi Guys, I'm building a stock portfolio tracker and am trying to decide the best approach for building a few tables and connecting to my data entry form.

    Issue 1: Table1 will have stock codes, but often between exchanges, different companies will have the same stock code.
    Issue 2: Table2 will have company names, many which will have different stock codes, allowing for their derivative components.
    Issue 3: Form1 is my data entry form, where I select or input a stock code, but I want another field to auto-populate it's associated company name, possibly also its associated exchange in a third field.



    How best do I achieve this in constructing my tables? I'm suspecting many-to-many relationships have to be mapped, but what about the auto-populating of other fields. I can build three tables of unique codes, names and exchanges, that's no problem, it's the joining and auto-populating that's got me questioning the methodology.

    Sorry if this might be a complicated question. Would anyone have any suggestion approaches to this. Thanks.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a link re Database Planning and Design etc. There's a lot of info that may be helpful to you.

    Good luck

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks Orange, That looks a great link.

    To be a tad more specific. If I have two tables 1: Stock Codes, and 2:Company Names, when in my data entry form, if I select a stock code from a ComboBox, what tutorial would help me understand how to populate another field with the associated Company Name of that selected Stock Code? I'm guessing there might some Index or LookUp function I'm unaware of. I might even have to concatenate both fields to a joined third table then link that table 3 to the form fields. Any thoughts? Thanks.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A Form is really a user interface to data that is stored in 1 or more tables. The key to database is to first design your tables and relationships based on the rules of your business.

    It would be helpful if you could describe in plain English (no jargon) what occurs when you buy a Stock with a specific symbol on an Exchange. I am not a finance nor stock market person, so giving readers a simple description of the processes and things involved would be the place to start. Pretend you are describing this to an 8 yr old-- key it simple-- and readers will help you with
    the design. (I do not know what derivatives and components mean, nor where they fit in your set up).

    I did find these links to try to get some grasp of the terminology, but they seem (to me) too complex and beyond the scope of your post.
    https://www.investopedia.com/article...erivatives.asp
    https://www.codeproject.com/Articles...arket-Data-Pro

    Regarding you questions:
    A combo box would get its data(rowsource) from a table or query.
    Populating a field in a table could involve an Append query and/or some code(vba).

    It all starts with tables and relationships that represent your business.

  5. #5
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Sorry Orange. All stocks have a code associated with their business name. For example. In Australia, we have a stock called Commonwealth Bank. This is the company name and it's stock code for trading on the market is CBA. However some companies trade in multiple countries, such as BHP Billiton. Its code is BHP across Australia, US and UK. However, each code is designated an exchange extension to identify which exchange it trades in. For Australia it's .AX, US would be .US and UK would be .UK. In fact, I realsied tonight when creating my stock table, to just add the country extension to each code keeping it unique, such as BHP.AX, BHP.US and BHP.UK. In my second table, I would just input the company's name with their company extension also keeping them unique. So, BHP across the three countries would be named BHP Biilliton LTD (Australia), BHP Billiton LLC (US), and BHP Billiton PLC (UK). There's my two tables of unique values. To complicate it further, each company also has derivative products that trade on the same exchange, such as options and warrants. So BHP in Australia might have extra codes, BHPOZL.AXW and BHPOPZ ect.

    So in my form, I have one ComboBox sourced to my stock code table and another ComboBox sourced to my company name table. Now, simply, when I select a stock code, I'd like my second ComboBox to recognise this and automatically select the correct associated company name to that code to save me manually selecting its name. That's it, what I'm trying to achieve.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show your table designs. You can expand your table designs and capture a PrintScreen as a jpg file and attach to post.

    I see these potential tables based on your description.

    StockSymbol
    Exchange
    Trade

    I still do not understand Derivative, Option and Warrant

    A little reading also identified these:

    • Symbol - Security symbol (e.g. BHP)
    • Exchange - Exchange the trade occurred on (e.g. ASX, CXA)
    • Price - Transaction price
    • Quantity - Transaction quantity
    • Time - Transaction date and time (this will be in milliseconds or microseconds if it's a good dataset)
    • Trade Type (Condition Codes) - What type of trade it was (e.g. standard, off-market trade report, booking purpose trade)

  7. #7
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi orange, spot on. These trade features, I've already created individual tables for and linked them to a master Trade Entry table, and linked that to a Trade Entry form.

    Now, each stock symbol also has a stock name and each stock name may have have multiple symbols assigned to it to designate either a stock, warrant or option. Stock codes in Australia have three letters while their derivatives will have five and six letters.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about a copy of the database with say 3 or 4 records per table? I'm not interested in your application, but if you want help we need to see the structures you are dealing with or want, and some test data to verify any code or designs.

    I have asked for tables and relationships, and a simple overview of "what you do with these things", but you keep mentioning Form.

    Do you keep buy sell info?
    Do you record end of day values?

    The only stuff I did with Finance data is here.

  9. #9
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Sure, here is part of the entry form.

    Click image for larger version. 

Name:	Portfolio Entry Screen.jpg 
Views:	12 
Size:	48.2 KB 
ID:	35160

    At the moment, the Ticker Code and Asset Name are from two different tables and when entering a new trade I manually select the code and name separately. I'd like that when I select the ticker code, the name automatically populates.

    Click image for larger version. 

Name:	Ashampoo_Snap_2018.08.18_23h57m16s_012_.jpg 
Views:	12 
Size:	28.5 KB 
ID:	35161

    Click image for larger version. 

Name:	Ashampoo_Snap_2018.08.18_23h58m36s_014_.jpg 
Views:	12 
Size:	29.2 KB 
ID:	35162

    Here is also the master table structure
    Attached Thumbnails Attached Thumbnails TradeDataTable.jpg  
    Last edited by DigitalAdrenaline; 08-18-2018 at 10:20 PM.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Every table should have a Primary Key to uniquely identify each record in that table.

    You said table1 and table2 in your first post. You identify the table graphic as master table?

    What is the rowsource of the ticker code combo on your entry form?

    Basic setup
    Click image for larger version. 

Name:	CompanyStockTradeByExchange.png 
Views:	11 
Size:	20.5 KB 
ID:	35165
    Last edited by orange; 08-18-2018 at 12:27 PM.

  11. #11
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    In your Company table you have company name and symbol but the company name is missing from TradeDetail. I would like both to appear within TradeDetail which is why I've separated the Symbol and CompanyName to respective separate tables. Also the Company Table would need to hold thousands of company names and their respective symbols.

    The rowsource for the Ticker combo is AssetTickerFK

    Also, the date time stamp is an excellent idea but looking at the existing date format (set to medium), I'd much prefer to have the medium format with the time after it which isn't available from the current selection. I'd like it to display:
    05-Nov-98 5:23:34pm. Is this format possible to display? This would create an excellent tradeID in itself as a secondary audit flow.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You use a query to get data from tables. If you want the CompanyName, you can get it with the query below
    by joining the tables on the common field CompanySymbol which in my example is the PK of Company and a FK in TradeDetail.

    Code:
    SELECT  CompanyName 
    From  Company INNER JOIN TradeDetail ON
    Company.CompanySymbol =TradeDetail.CompanySymbol
    WHERE TradeDetail.CompanySymbol ="BHP"
    Here is a description of JOIN types from https://www.w3schools.com/sql/sql_join.asp

    Click image for larger version. 

Name:	w3shools_Joins.png 
Views:	11 
Size:	100.3 KB 
ID:	35168

  13. #13
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Ok. Thanks. let me digest that.
    Last edited by DigitalAdrenaline; 08-18-2018 at 10:19 PM.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Sorry Orange, was fixing a separate issue which is now done. All good but will head to be now and will run your SQL tomorrow. Will get back to you sometime then. Many thanks.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining tables help
    By grewpar in forum Access
    Replies: 7
    Last Post: 10-07-2014, 12:11 PM
  2. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  3. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  4. Good programming practice wrt lookup tables?
    By Buakaw in forum Programming
    Replies: 10
    Last Post: 03-19-2011, 10:33 AM
  5. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums