Solved Help setting up a new Investment Portfolio database using normalization

[HalloweenWeed]

Good day all,
I am making a personal investment recording/stat database for myself. I want to make a form that 'browses' the transactions, so I can easily and quickly verify that my database is working properly. I have a transactions table, a table for the long text descriptions, and account tables for each source/destination transaction. I would like to make a form with subreports to report on the contents of each of these tables, one transaction at a time (and it would also be nice to have a set of 'previous balance' fields as well, but this is optional). The problem is, using a traditional subreport container they will not change table names as needed (the accounts). I have mulled over putting field(s) as a parameter(s) from my master form as sql criteria to select the proper account table, but I don't know how to make that work. I know that since the subreports will load before the master form, the queries will fail and the form will fail to load. I'm looking for suggestions here.

So I would appreciate any thoughtful suggestions as to how to accomplish this. I am good at opening recordsets and SQL queries, and also good at using VBA to fill controls that are otherwise without "sources." Would you suggest that I write an SQL code for my account subform/subreport and call it within vba? How should this work?


EDIT: Never mind for now, I just realized that I can just create fields in the form, and use vba recordsets that I can change as needed via SQL in VBA to control the fields, as I change master record numbers.

 

[theDBguy]

Pardon me but it sounds like you may be working harder than necessary.

 

[plog]

Agree with DBguy. I don't think you have set up your tables properly.

My guess is all these tables you want to switch among have the exact same structure, and you are using the name of the tables to differentiate them. That is wrong. When you feel the need to have a set of tables with the exact same structure, you don't differentiate them using the table name, you differientiate them with a new field that holds whatever value you are using as each tables name.

Car analogy: If you had an inventory of cars you wouldn't have a Ford table, a Chevy table, a Buick table, etc etc. Instead you would have just one table for all your cars with another field for what make it is and put Ford, Chevy, Buick etc in there. I bet the same thing applies to your data.

 

[HalloweenWeed]

Pardon me but it sounds like you may be working harder than necessary.

Perhaps. I have lots of pretty fancy stats I want to eventually track on my investments, such as gain & loss, taxes paid, current cost per share, dividends, per acct. and total of all investments, annual as well as total and per transaction, and the usual tracking of performance, etc.

 

[HalloweenWeed]

Agree with DBguy. I don't think you have set up your tables properly.

My guess is all these tables you want to switch among have the exact same structure, and you are using the name of the tables to differentiate them. That is wrong. When you feel the need to have a set of tables with the exact same structure, you don't differentiate them using the table name, you differientiate them with a new field that holds whatever value you are using as each tables name.

Car analogy: If you had an inventory of cars you wouldn't have a Ford table, a Chevy table, a Buick table, etc etc. Instead you would have just one table for all your cars with another field for what make it is and put Ford, Chevy, Buick etc in there. I bet the same thing applies to your data.

Oh, OK. I am considering that now.

So instead of making separate tables, I just make two more fields in my account template, account number and account name. Yes, I see how that would work better now.

 

[HalloweenWeed]

I am structuring this similar to accounting method, where I have a debit entry that matches the credit entry. So I need to make a form that makes two record entries simultaneously? I can do that in vba without a problem, but is there a way to do that in vanilla tables?

MODERATOR: Perhaps this thread should be moved to another thread? It is taking a different turn than I expected.

 

[HalloweenWeed]

I was originally planning to use a transaction table, showing many binary properties of each transaction among other properties like transaction date & timestamp, broker ID, stock split ratios and amounts, and the 2 accounts of each transaction. Do you think I should keep that, or somehow just simply absorb it into the accounts table? The binary values are such as cash Xaction, tax included, foreign tax, security purchase/sale, stock split/reverse split, dividend, etc.

 

[ebs17]

have lots of pretty fancy stats I want to eventually track on my investments, such as gain & loss, taxes paid, current cost per share, dividends, per acct. and total of all investments, annual as well as total and per transaction, and the usual tracking of performance, etc.

That sounds as if this can be solved with the usual queries, without all the snooty stuff around it.
The prerequisite is, of course, that the tables are properly structured.

 

[plog]

Give setting up your tables your best shot in Access. Then complete the Relationship Tool, expand all the tables so we can see all fields in every table, take a screenshot and post it back here. That will help us help you with table structure.

 

[The_Doc_Man]

I am structuring this similar to accounting method, where I have a debit entry that matches the credit entry. So I need to make a form that makes two record entries simultaneously? I can do that in vba without a problem, but is there a way to do that in vanilla tables?

MODERATOR: Perhaps this thread should be moved to another thread? It is taking a different turn than I expected.

(Putting on Moderator hat): No need to move it. Working just fine for the moment. And it has a group of people giving it attention.

(Taking off Moderator hat): You seem to have an issue with the concept of normalization. You might benefit by diverting for a brief time to read some articles on the subject. In this forum, you can look up "normalization" because this IS a database forum. In general web searching, you would need to look up "database normalization" because that would screen out articles on other disciplines that also use the term "normalization" - such as chemistry, math, diplomacy, psychology, ... and there are others.

Normalization saves you from bloating your database with duplicated data and ALSO saves you from having to write overly complex code that does the same exact thing to many different tables that have the same purpose and structure. Further, since Access was designed for small businesses, it is important to not overload it with too many objects. With a good design, an Access database can handled thousands or tens of thousands of things, and some of us reached the 100k/1million record range. So it has good data handling abilities - but with poor data design it can become a bear to manage. Which is why I suggested reading about normalization. That will help you design better table structures to have that kind of capacity and yet have relatively easy code to do things... selectively or wholesale.

 

[Gasman]

Have a look at these links posted by Micron.

Making a query updatable

Hello to everyone. I'm having trouble to make a query updateable, and I'd like some help, if there's a solution for it. I have my main customers table (tbl_Customers) and two tables (tbl_DateA , tbl_DateB) that represent event dates related to the customers. (each table represents completely...

www.accessforums.net

 

[HalloweenWeed]

I'm having trouble redesigning this to work off of 1 table, plus an account directory table and a broker table. Here's what I can't seem to understand. Each transaction has a credit account and a debit account, and the running total amount of each is subtracted or added on each transaction. So to do that in one table, it would require two entries, one per account. I don't want to enter all transactions twice, so how do I do that on one single table, without getting fancy with the vba?

 

[Pat Hartman]

I have lots of pretty fancy stats I want to eventually track on my investments, such as gain & loss, taxes paid, current cost per share, dividends, per acct. and total of all investments, annual as well as total and per transaction, and the usual tracking of performance, etc.

Each transaction should have one quantity field. Positive or negative, indicating a purchase or sale. If you are talking about "moving" assets from one account to another, the user would enter one side of the transaction on a separate form and your code would create the corresponding offset for the other account.

Post what you have so far for the tables. Including an actual database is always more useful than just a picture. Use test data rather than your actual account numbers and details.

 

[ahmedjamalaboelez]

If I understand well ,
you do not need to suffer with sub form for statistics from multiple tables and multiple Sql , Just use Active x Control List View and you can handle the code , Sql Statement , Headers , and data

Option Compare Database
Option Explicit
'<<---- Define Listview For Publish
Dim lvwList    As MSComctlLib.ListView
Dim lvwItem    As MSComctlLib.ListItem
Dim ObjImgList As MSComctlLib.ImageList

'$<<-- Get List Views Search
Public Function LoadListView(Optional FrmIDx As String)
    Dim rs     As ADODB.Recordset
    Dim cmd    As New ADODB.Command
    cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdText
    If Trim(FrmIDx & "") = vbNullString Then
        If Len(Me.txtoffice.Value & "") = 0 Then
            cmd.CommandText = "SELECT opty_id, opty_date, site_name_en, salp_name, cust_name_en, user_fullname, opty_net_amt, opty_flow_process, opty_close_status " & _
                              "FROM oportunity_dta_search " & _
                              "WHERE opty_id + opty_scope + site_name_en + salp_name + cust_name_en + user_fullname Like '%" & Replace(Nz(Me.txt_search, ""), "'", "`") & "%' " & _
                              "AND opty_flow_process Like '%" & Me.txtflowproc & "%' " & _
                              "AND opty_close_status Like '%" & Me.txtclosestatus & "%' " & _
                              "AND opty_site_id IN (Select usite_site from users_sites WHERE ussite_user = '" & TempVars!SysUserID & "') " & _
                              "AND opty_salp_id Like '%" & Me.txtsalp & "%' " & _
                              "AND opty_c_user Like '%" & Me.txtuser & "%' " & _
                              "AND opty_cust_id Like '%" & Me.txtcust & "%' " & _
                              "AND opty_date >= '" & Format(Me.txtstartdate, "YYYY-MM-DD") & "' " & _
                              "AND opty_date <= '" & Format(Me.txtenddate, "YYYY-MM-DD") & "' " & _
                              "Order By opty_id"
        Else
            cmd.CommandText = "SELECT opty_id, opty_date, site_name_en, salp_name, cust_name_en, user_fullname, opty_net_amt, opty_flow_process, opty_close_status " & _
                              "FROM oportunity_dta_search " & _
                              "WHERE opty_id + opty_scope + site_name_en + salp_name + cust_name_en + user_fullname Like '%" & Replace(Nz(Me.txt_search, ""), "'", "`") & "%' " & _
                              "AND opty_flow_process Like '%" & Me.txtflowproc & "%' " & _
                              "AND opty_close_status Like '%" & Me.txtclosestatus & "%' " & _
                              "AND opty_site_id Like '%" & Me.txtoffice & "%' " & _
                              "AND opty_salp_id Like '%" & Me.txtsalp & "%' " & _
                              "AND opty_c_user Like '%" & Me.txtuser & "%' " & _
                              "AND opty_cust_id Like '%" & Me.txtcust & "%' " & _
                              "AND opty_date >= '" & Format(Me.txtstartdate, "YYYY-MM-DD") & "' " & _
                              "AND opty_date <= '" & Format(Me.txtenddate, "YYYY-MM-DD") & "' " & _
                              "Order By opty_id"
        End If
    Else
        cmd.CommandText = "SELECT opty_id, opty_date, site_name_en, salp_name, cust_name_en, user_fullname, opty_net_amt, opty_flow_process, opty_close_status " & _
                          "FROM oportunity_dta_search " & _
                          "WHERE opty_id = '" & FrmIDx & "'"
    End If
    Set rs = cmd.Execute
    Set lvwList = Me.list_search.Object
    With lvwList
        .ColumnHeaders.Clear
        .Font.Name = "Segoe UI"
        .Font.Size = 8
        .BackColor = 16183786
        .ColumnHeaders.Add , , "ID", 2350
        .ColumnHeaders.Add , , "Date", 1100
        .ColumnHeaders.Add , , "Office", 1800
        .ColumnHeaders.Add , , "Customer", 3600
        .ColumnHeaders.Add , , "Sales Person", 2100
        .ColumnHeaders.Add , , "User", 2100
        .ColumnHeaders.Add , , "Amount", 1000
        .ColumnHeaders.Add , , "Flow Status", 2000
        .ColumnHeaders.Add , , "Status", 800
    End With
    While lvwList.ListItems.Count > 0
        lvwList.ListItems.Remove (1)
    Wend
    Do Until rs.EOF
        Set lvwItem = lvwList.ListItems.Add(, , rs!opty_id, rs!opty_flow_process.Value, rs!opty_flow_process.Value)
        lvwItem.ListSubItems.Add , , Format(rs!opty_date, "DD/MM/YYYY")
        lvwItem.ListSubItems.Add , , rs!site_name_en
        lvwItem.ListSubItems.Add , , rs!cust_name_en
        lvwItem.ListSubItems.Add , , rs!salp_name
        lvwItem.ListSubItems.Add , , rs!user_fullname
        lvwItem.ListSubItems.Add , , Format(rs!opty_net_amt, "Standard")
        lvwItem.ListSubItems.Add , , rs!opty_flow_process
        lvwItem.ListSubItems.Add , , rs!opty_close_status
        rs.MoveNext
    Loop
    Set lvwItem = Nothing
    rs.Close
    Set rs = Nothing
End Function

 

[HalloweenWeed]

Agree with DBguy. I don't think you have set up your tables properly.

My guess is all these tables you want to switch among have the exact same structure, and you are using the name of the tables to differentiate them. That is wrong. When you feel the need to have a set of tables with the exact same structure, you don't differentiate them using the table name, you differientiate them with a new field that holds whatever value you are using as each tables name.

Car analogy: If you had an inventory of cars you wouldn't have a Ford table, a Chevy table, a Buick table, etc etc. Instead you would have just one table for all your cars with another field for what make it is and put Ford, Chevy, Buick etc in there. I bet the same thing applies to your data.

I am structuring this similar to accounting method, where I have a debit entry that matches the credit entry. So I need to make a form that makes two record entries simultaneously? I can do that in vba without a problem, but is there a way to do that in vanilla tables?

Give setting up your tables your best shot in Access. Then complete the Relationship Tool, expand all the tables so we can see all fields in every table, take a screenshot and post it back here. That will help us help you with table structure.

This is the part I am talking about now, I cannot even create the main table because I do not understand how to do it without separate tables for accounts. I've attached what I have. I can't finish the relationships because I can't join both the debit account ID and the credit account ID to the account directory, let alone the tax account and the fee account. I can't use your recommendations above if I don't understand how to do the transactions. Am I going to rely on vba to do 3 other transactions on every entry that has a tax and a fee? Doing manual double-entries leads to errors and I refuse to go that way. Can you be more forthcoming about how to achieve this (easily) please?

If I understand well ,
you do not need to suffer with sub form for statistics from multiple tables and multiple Sql , Just use Active x Control List View and you can handle the code , Sql Statement , Headers , and data

Thank you @ahmedjamalaboelez, I appreciate your contribution.
But since I restarted from the beginning, I am not that far yet. You have some very interesting style there! Very nice.

 

[mike60smart]

This is the part I am talking about now, I cannot even create the main table because I do not understand how to do it without separate tables for accounts. I've attached what I have. I can't finish the relationships because I can't join both the debit account ID and the credit account ID to the account directory, let alone the tax account and the fee account. I can't use your recommendations above if I don't understand how to do the transactions. Am I going to rely on vba to do 3 other transactions on every entry that has a tax and a fee? Doing manual double-entries leads to errors and I refuse to go that way. Can you be more forthcoming about how to achieve this (easily) please?


Thank you @ahmedjamalaboelez, I appreciate your contribution.
But since I restarted from the beginning, I am not that far yet. You have some very interesting style there! Very nice.

Hi
When you enter details for 1 Record you currently have the following fields as Yes/No Data Types.

CashTrnsact

CashDpst

CashWthdrwl

CashShft

CashCore

ScrtyPrchs

ScrtySale

Divdnd

Split

TaxTrnsactnOnly

XactnRvrsl

Would you select 1 or more of these for Each Record?

 

[HalloweenWeed]

Hi
When you enter details for 1 Record you currently have the following fields as Yes/No Data Types.
...
Would you select 1 or more of these for Each Record?

Yes. It designates the transaction type. So far, I have not encountered any type of transactions that would not be covered, and don't anticipate any.

Oh, to elaborate: a cash transaction would be either deposit, withdrawal, or shift - a transfer to a new or different cash account within the same broker. I have the one binary separate because most statistics will ignore all cash transactions, it's less complicated to make the SQL.

CashCore represents the currently designated "cash core account" for that broker, also the one that is used by default on all securities purchases and sales. It should be queried before any securities transaction to determine the set account to be used with the securities account for the transaction. This "cash core account" could be set to a different account at any date. Having the binary in the transaction table may be necessary for debug should there be an issue with the selected transaction cash account used for a securities purchase or sale. I'm not sure if it will be necessary to keep it beyond the final database build.

 

[mike60smart]

So you would select 1 Transaction Type?
If this is true then the list of Transaction Types should be in a separate table.
All of the listed fields should be deleted from "tblXactn"
In your "tblXactn" you would have a field "TransactionTypeID" with Number DataType.
Then on your Data Input Form you would have a Combobox to allow you to select 1 of the Transaction Type Values.

 

[HalloweenWeed]

So you would select 1 Transaction Type?
If this is true then the list of Transaction Types should be in a separate table.
All of the listed fields should be deleted from "tblXactn"
In your "tblXactn" you would have a field "TransactionTypeID" with Number DataType.
Then on your Data Input Form you would have a Combobox to allow you to select 1 of the Transaction Type Values.

Thank you. Will do.

One thing though, I hate Access comboboxes. I tend to get elaborate with the vba to avoid their use.

 

[Pat Hartman]

One thing though, I hate Access comboboxes. I tend to get elaborate with the vba to avoid their use.

Do you hate them because you don't understand how they work? They are the absolutely simplest way to select an item from a list. Do you not understand how to use a table to manage the list of items? There are things to hate about Access but I would never have thought of including combos in that list.