Totals query combined with other queries (1 Viewer)

JahJr

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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
DATESYMBOLPRICE
9/30/2020A1
9/30/2020B2
9/30/2020C3
9/30/2020D4
9/30/2020E5
10/1/2020A10
10/1/2020B20
10/1/2020C30
10/1/2020D40
10/1/2020E50

The transactions table shows all buys or sales for each symbol.
DATESYMBOLSHARES
8/1/2020A100
8/1/2020B100
8/1/2020C100
8/1/2020D100
8/1/2020E100
8/15/2020A25
8/17/2020B-50
8/17/2020C100
8/20/2020D10
10/1/2020A-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
DATESYMBOLSHARESPRICETOTALVALUE
10/1/2020B50201,000
10/1/2020C200306,000
10/1/2020D110404,400
10/1/2020E100505,000
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
42,981
Create a query that selects the price by date.
Create a query that summarizes the number of shares by symbol.
Join these two queries and multiple price by shares to get total value.
 

JahJr

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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.
DATEPRICES.SYMBOLSHARESHELD.SYMBOLSUMOFSHARESCLOSE
10/1/2020BB1,00020
10/1/2020CB1,00030
10/1/2020DB1,00040
10/1/2020EB1,00050
10/1/2020BC6,00020
10/1/2020CC6,00030
10/1/2020DC6,00040
10/1/2020EC6,00050
10/1/2020BD4,40020
10/1/2020CD4,40030
10/1/2020DD4,40040
10/1/2020ED4,40050
10/1/2020BE5,00020
10/1/2020CE5,00030
10/1/2020DE5,00040
10/1/2020EE5,00050

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

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
42,981
Create a query that selects only the most recent dividend so that you have one and only one rows per symbol. Then use a left join to join the two-table query above using a left join to this new dividend query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:18
Joined
May 7, 2009
Messages
19,169
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

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
Dividend Table Example
DATESymbolAmount
8/1/2020A.50
8/1/2020C1
8/16/2020A.51
8/16/2020C1.01
10/1/2020A.52
10/1/2020C1.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.
DATESYMBOLAMOUNTSHARESTOTALRECEIVED
8/1/2020A.5010050
8/1/2020C1100100
8/16/2020A.5112563.75
8/16/2020C1.01100101
10/1/2020A.5200
10/1/2020C1.02200204

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

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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.
SQL:
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]);
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
42,981
The date criteria doesn't make any sense. You are comparing the date to itself. You should be comparing the date to a control on a form or a date that you prompt for.

FYI. I didn't suggest domain functions because they are quite inefficient when used this way. If you have a hundred symbols in the query, Access will have to run a hundred queries. Totals queries which you then join to are far more efficieng.
 

JahJr

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
42,981
So, you've now changed your request to calculating a running sum.

You can control date ranges in a query by referencing form controls

Where SomeDate Between Forms!yourform!txtFromDT and Forms!yourform!txtThruDT
 

JahJr

Andy
Local time
Yesterday, 19:18
Joined
Dec 3, 2008
Messages
93
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
42,981
A sample db always helps along with the recordset you are looking for as a result.
 

Users who are viewing this thread

Top Bottom