Totals query combined with other queries

[JahJr]

I have an access db that has the following tables
Symbols
Prices
Transactions

The symbols table shows all possible symbols

SYMBOL

A

B

C

D

E

The prices table shows closing price for all symbols for all dates

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

 

[JahJr]

I did that and I'm getting the following result. To further check where my problem was I included the Symbol column from both of the queries and I keep getting a result that looks like this.

DATE	PRICES.SYMBOL	SHARESHELD.SYMBOL	SUMOFSHARES	CLOSE
10/1/2020	B	B	1,000	20
10/1/2020	C	B	1,000	30
10/1/2020	D	B	1,000	40
10/1/2020	E	B	1,000	50
10/1/2020	B	C	6,000	20
10/1/2020	C	C	6,000	30
10/1/2020	D	C	6,000	40
10/1/2020	E	C	6,000	50
10/1/2020	B	D	4,400	20
10/1/2020	C	D	4,400	30
10/1/2020	D	D	4,400	40
10/1/2020	E	D	4,400	50
10/1/2020	B	E	5,000	20
10/1/2020	C	E	5,000	30
10/1/2020	D	E	5,000	40
10/1/2020	E	E	5,000	50

I just figured out how to get the correct result but I feel that I have other problems and I should not have to do this step. Please let me know your thoughts.
If I add Prices.Symbol to the SharesHeld.Symbol Criteria in the design feature I get the expected result.
Why do I have to do this?

 

[JahJr]

I've got the above working, I also have a table titled dividends. It has 3 Columns
DATE SYMBOL AMOUNT

I need to create a query that will tell me what the divided received is. The query would need to have a calculated column that was suming the number of shares held on the dividend date. then a column that would multiply the shares by the dividend amount.

I cant get the query to come up with the number of shares held on each dividend date.

 

[arnelgp]

create a query Left joining your query in post#3 to your table in post#4:

select query1.*, nz(table.amount, 1)*query1.sumofshares from query1 left join table on query1.date = table.date and query1.symbol=table.symbol;

 

[JahJr]

Dividend Table Example

DATE	Symbol	Amount
8/1/2020	A	.50
8/1/2020	C	1
8/16/2020	A	.51
8/16/2020	C	1.01
10/1/2020	A	.52
10/1/2020	C	1.02

I'm trying to figure out how much cash would be available on a particular date. Below is what I'm looking for and can't get a query to work for this.

DATE	SYMBOL	AMOUNT	SHARES	TOTALRECEIVED
8/1/2020	A	.50	100	50
8/1/2020	C	1	100	100
8/16/2020	A	.51	125	63.75
8/16/2020	C	1.01	100	101
10/1/2020	A	.52	0	0
10/1/2020	C	1.02	200	204

With the information from the query above I hope to be able to calculate Cash available for a given day
Cash Available on 8/1/2020 = 150
Cash Available on 9/30/2020 = 314.75
Cash Available on 10/15/2020 = 518.75

My apologies for not being more clear in the previous post.

 

[JahJr]

I've made it a little bit closer but still can't get it. I've got the following query which gives me the total of all shares for all dates. If I remove the date criteria it gives me the total of the all shares for the correct symbol. The date criteria is giving me problems.

SELECT HistoricalPricing.Symbol, HistoricalPricing.[B-Date], HistoricalPricing.Close, Dividends.Amount, Transactions.shares,
DSum("Shares","Transactions","HistoricalPricing.Symbol=""" & [HistoricalPricing].[Symbol] & """" And "#" &
[Transactions].[B-Date] & "#<=#" & [Transactions].[B-Date] & "#") AS SharesTotal

FROM (HistoricalPricing
    LEFT JOIN Dividends ON (HistoricalPricing.Symbol = Dividends.Symbol) AND (HistoricalPricing.[B-Date] = Dividends.ExDividend))
    LEFT JOIN Transactions ON (HistoricalPricing.Symbol = Transactions.Symbol) AND (HistoricalPricing.[B-Date] = Transactions.[B-Date]);

 

[JahJr]

Ok so i could use the date from my form but i still need to know how many shares were held on each dividend date. I don't know how I can do that with out comparing it to itself. If you look at my post #7 I don't see how i can get the results needed in the second table without creating some sort of sumifs column which would require looking at the date and the symbol.

 

[JahJr]

I don’t believe I’m changing my request. From my example I posted in #7 I’m needing to calculate the total cash from dividends. This calculation will always start at the beginning and need to figure out how many shares were owned on the dividend date and then multiply the shares owned by the dividend. It will need to run this calculation for every symbol up until the date that is entered in the user form. I’m trying to calculate how much cash is available on a date chosen by the user. The start date is always the earliest date available.

would it be better for me to post a sample DB?