I have an access db that has the following tables
Symbols
Prices
Transactions
The symbols table shows all possible symbols
The prices table shows closing price for all symbols for all dates
The transactions table shows all buys or sales for each symbol.
I have a user form that runs my Value query. The user form allows you to select a date. I can get a query to give me the total shares for a date or give me the prices for each symbol for a date but I can't get the information from those 2 queries combined into one query. Below are the results I'm looking for. Symbol A is not shown because on 10/1/2020 its shares added up to 0.
Expected result for 10/1/2020
Symbols
Prices
Transactions
The symbols table shows all possible symbols
SYMBOL |
A |
B |
C |
D |
E |
DATE | SYMBOL | PRICE |
9/30/2020 | A | 1 |
9/30/2020 | B | 2 |
9/30/2020 | C | 3 |
9/30/2020 | D | 4 |
9/30/2020 | E | 5 |
10/1/2020 | A | 10 |
10/1/2020 | B | 20 |
10/1/2020 | C | 30 |
10/1/2020 | D | 40 |
10/1/2020 | E | 50 |
The transactions table shows all buys or sales for each symbol.
DATE | SYMBOL | SHARES |
8/1/2020 | A | 100 |
8/1/2020 | B | 100 |
8/1/2020 | C | 100 |
8/1/2020 | D | 100 |
8/1/2020 | E | 100 |
8/15/2020 | A | 25 |
8/17/2020 | B | -50 |
8/17/2020 | C | 100 |
8/20/2020 | D | 10 |
10/1/2020 | A | -125 |
I have a user form that runs my Value query. The user form allows you to select a date. I can get a query to give me the total shares for a date or give me the prices for each symbol for a date but I can't get the information from those 2 queries combined into one query. Below are the results I'm looking for. Symbol A is not shown because on 10/1/2020 its shares added up to 0.
Expected result for 10/1/2020
DATE | SYMBOL | SHARES | PRICE | TOTALVALUE |
10/1/2020 | B | 50 | 20 | 1,000 |
10/1/2020 | C | 200 | 30 | 6,000 |
10/1/2020 | D | 110 | 40 | 4,400 |
10/1/2020 | E | 100 | 50 | 5,000 |