Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    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

    Yes I saw that. Don't have any suggestions for you. Something must be interfering with the calculation or something may be corrupted??
    However, as I said previously, if you post a copy of your database with only a few records, readers can see, analyze and test some options. Readers are not interested in your application itself, but can often assist more efficiently with a physical example.

  2. #17
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi Orange, I suspect the field was corrupted so I deleted it and recreated it which fixed the issue. All good now. I'll start working with your provided solution. I just need to understand the table structure and coding syntax otherwise I'm (mentally) shooting in the dark.

    ok. The first issue: Each Company can have several symbols designating different investment products attached to it. For example, just using BHP.

    Table1 - Company: BHP Billiton
    Table2 - Symbol: BHP (for shares), BHPOA (for an Option), BHPKMZ (for a Warrant) etc etc, and
    Table3 - Product: (each product also has many symbols designated to it for each company) Shares, Options, warrants, CFDs, Futures etc etc
    Table4 - Exchange: AX (for Australia), US (for United States), UK (for United Kingdom)

    In short: each company has different investment products with multiple product symbols.

    In fact, I could link the Symbol table to the Product table, link the product table to the exchange table, and the exchange table to the Trade table. It would still require four selections though. There really won't be any shortcut in reducing mouse clicks or selecting the trade features, except linking the tables as I've noted would reduce selection error which is still important. Will play with this further.

  3. #18
    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
    My diagram was not a solution -it was an attempt to show the meaning of an inner join and how tables can be "joined" to get related info from those "joined" tables.
    There is a lot of info in the Database Planning and Design link from concepts to analysis to design techniques.
    One of the RDBMS principles is to have "atomic data" (each field represents a single fact). Another is to not have redundant data.
    If a Company can have many symbols each identifying the Company and one of its InvestmentProducts, then the database design should reflect it.

    I am not familiar with the Stock/Securities/Exchange terminology, but for database design and efficiency you should have all info about a specific concept/place/thing in a single table. How those things are related (your Business facts/rules) will identify the relationships between your tables.

    Here are a few things to consider:

    Company--->CompanyInvestmentProduct<---InvestmentProduct
    Exchange is a different concept/entity and would be a table.

    tblCompany

    CompanyId PK
    CompanyName (this would be BHP Bilton...)
    OtherCompanyInfo

    tblInvestmentProduct
    InvestmentProductID PK
    InvProdName (this would be Share,Option, Warrant, etc)

    tblCompanyInvestmentProduct
    CompIvstProdID PK
    This would have a unique composite index based on CompanyID,InvestmentProductID
    It would contain other info of interest to you


    tblExchange
    ExchangeID PK
    ExchangeCountry (this would be AUS, US, UK....)
    ExchangeName ( could NYSE,ASX.....)
    OtherExchangeInfo


    See this link for a Stocks and Funds Trading Data Model
    Last edited by orange; 08-19-2018 at 06:53 PM. Reason: spelling

  4. #19
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi Orange, all ok. You are very correct and these conventions are what I'm seeking to stick to. Below are my table lists allowing for all unique values. Brokers may require to be a many-to-many relationship but it's not critical. Here is what I think the logical table flow should be (so far).

    Click image for larger version. 

Name:	TableFlow.jpg 
Views:	8 
Size:	42.6 KB 
ID:	35179

  5. #20
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Orange, a curious question just hit me. If I'm importing my Excel data to Access, splitting the Excel columns to two Access tables, say EntryDate to my Trade table and my symbols to my Symbol table, how will it match up the two columns in my main Trade table? It looks like once the EntryDate is imported I'll still have to manually select all my associated symbols that I imported to the Symbol table so all the records line up. Is there a way after importing my symbols that I can automatically link them to my original EntryDate record? Does that make sense? Thanks.

Page 2 of 2 FirstFirst 12
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