How do I summarize data from two tables based on multiple criteria?

[pholt33]

I have two tables where I want to combine values based on AccountID and also SecurityID. The first two screenshots are my table setups and the third screenshot is how I ultimately want the data to look.



What I am trying to get to is a person's current holdings (Shares and Values) based on initial holdings and subsequent transactions. If you think of your personal Vanguard/Fidelity/whatever investment account, I want the summary information that is on the quarterly statements based on the account activity during that time period.

If it would be possible to combine all the information into a single table rather than having two tables, that would be ideal.

How can I do this either in a query and/or as a single-table setup?

Thanks for helping an Access dummy!

[June7]

Your images do not appear to have posted. I don't see any - only red X in their places.

[Dal Jeanis]

Don't try to merge the tables - small tables that have carefully determined relationships is the key to an accurate and useful database.
Actually, you have too many data fields on the tables.

There should be another table that links AccountOwner and AccountID, and only the AccountID should be on these tables. Likewise, SecurityID, SecurityType and SecurityName should be in a separate table, and only SecuityID should be on these tables.

InitialValue is inadequate, and your current value calculation is wrong, accountingwise. You need two fields, InitialBasis and InitialValue. There needs to be a table somewhere that has the current value of any given Security, and currentvalue is number of shares times that, Initialvalue is the number of shares times the current value at the beginning of the period. There needs to be another column, CurrentBasis, for the current accounting cost of the shares remaining at the end of period.
In fact, you need to determine whether you are using LIFO or FIFO or average basis value, in order to properly determine the basis after any given sale. The drop in Basis needs to be a field calculated at any sale.

Sorry to give you the bad news, but this structure doesn't do proper tax accounting. If you buy 2000 shares at $10 and sell $1000 shares at $20 the current value ends up zero.

[pholt33]

I will split the tables tomorrow and then see if I can properly link them back together using only the appropriate/minimum fields required. One I do that in the morning, I'll report back here to figure out the current (or as of a specified date) holdings.

As far as the specific accounting, that's a minor concern for the purposes of this database. I can probably pull security prices from a separate database, but that can come at a later step.

[pholt33]

Is this how you would recommend setting up the tables?

[Dal Jeanis]

Mostly. Add a security table with SecurityID as the primary key and SecurityName and securityType as fields. Remove SecurityType from Holdings and Transactions.

Naming convenstions - I'd suggest putting "tbl" on the front of the names of the tables. Also, since "Date" is a reserved word, I'd suggest HoldingDate and TranDate as better names for the date fields.