Yes, typically a dividend will occur once a month.
The dividend would refer specifically to a particular fund.
The investment has the particular RegID (person purchasing), FundID (particular fund they are purchasing), UnitsPurchased (how many they are buying), and Date (Date transaction took place).
So, to answer your third question, yes...? A person (i.e. RegID) can invest in multiple funds (i.e. FundID) and can invest...
kabradley
Last Activity: Mar 28 '08, 08:13 PM
Joined: Feb 7 '07
Location:
-
SQL Stock Query - Summing fields to use in Multiplication
Hello all!
I have a logic problem that I have been working on for quite some time now and can't seem to fix. Hopefully someone can shed some light on the subject.
I am starting to work on an access database that will hold stock data. The database needs to be able to do the following:
Compute the correct amount of stock share dividends that each individual receives, at runtime.
Let me explain further. There... -
I know Adrian, that query is massive!
Mary, I didn't get an email, but I pm'ed you with another address to try.
AllenLeave a comment:
-
Alrighty Mary, the most current SQL statements are as follows
Union Query
[code=sql]
SELECT tblPortfolio.Po rtfolioName, RegistrationID, tblRegistration .RegistrationNa me, Investments.Inv estmentID, Investments.LPU nitsOwned, Investments.Inc eptionDate, LPID.VendorFami ly, LPID.LPName, LPID.LPUnitPric e, LPCatagories.LP Catagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Distributions.D ay, Distributions.M onth,...Leave a comment:
-
Bumping to the top.
Hope to hear from you soon, thanks Mary
Allen :)Leave a comment:
-
Hi Mary,
Just wanted to remind you about this thread. I know you have tons going on, but was hoping that you may have time to look at it today.
Just let me know.
Thanks!
AllenLeave a comment:
-
Well Adrian,
After some more fiddling with it I was able to get it to narrow down selections. I am honestly not sure what I did different compared to this morning, but whatever it was it is working now. Thanks for the CDATE tip. Definitely will have to remember that.
Allen...Leave a comment:
-
Bucabay
I'm not sure I'm understanding what you are suggesting. I don't understand what you mean by "each file upload". What file are they uploading? And when do they upload it? What download are they making? Is the download the update? When would all this be happening? Are you suggesting that the database information was client side BUT it would be replicated on a SQL server? That way when the information differs on the client computer...Leave a comment:
-
Adrian,
wow, just wow. I searched the internet all over for that simple answer /shakes head
Thanks for that extremely simple piece of code. In the "criteria" area of the query I entered [newDate]>[inceptionDate] but this did not seem to filter the information correctly. I knew that sometimes using functions with dates can present a problem so I searched the net for an answer, but through my reading I didn't see anything...Leave a comment:
-
Hey Adrian,
So let me just double check that I am understanding what you are suggesting. The query that Mary wrote up top, I am to use that in another query. In this second query I am to join the query and a table that has all the months listed. Joining them in such a way that "All records in tblMonth show and only those in the query that are equal show". Is this correct?
Assuming it is, I did that this morning made...Leave a comment:
-
I too have been wondering about this.
In my head it should work like so:
Access database opens, in the OnLoad event it would access a ftp site or a site allowing downloads. On the site there would be downloads that would contain SQL statements.(not sure what the file extention would be for something like that) The Database would the proceed to download the SQL statement and once downloaded it would run the SQL code. The code...Leave a comment:
-
Alright will do.
Thanks...Leave a comment:
-
Mary
/shakes head
Well I made a report that used the crosstab query that I made using the union query above. After making it and running it I ran into a problem that I had completely not realized when looking at the queried data. Because of how the crosstab gathers the information on any years that don't have tax deductions it has a $0.00 amount which is fine, EXCEPT when the year has a tax deduction because what ends up happening is...Leave a comment:
-
Wow!
So the query finally works !!! Thank you so much for all of this help Mary! I appreciate it so very much and am so glad that you were willing to stick with me through the whole thing since it was a little bit more complex of a problem than I thought it was to begin with. I greatly, greatly appreciate it!!
I've spent a good amount of time trying to understand the sql statement, more specifically the joins in the second part of the...Leave a comment:
-
Oh Great!
Thanks for the update, more than anything I was just trying to see where we stood, but if you could look at it today that'd be marvelous.
Thank you...Leave a comment:
-
Hey Mary,
Just wanted to see how your work load was coming and whether or not you thought you'd be able to looka t my problem again this week.
Thanks,
AllenLeave a comment:
-
Oh my! Well Adrian, hopefully you and Mary are getting near completion. I really like the new roll over buttons at the top. That's a pretty classy touch and is actually useful! Form AND function! There are a few other things that I've been working on around the office, so I'm definitely not twidling my thumbs. :) I know making major changes to a site with minimal downtime takes a lot of work and planning and if it weren't for this wonderful site...Leave a comment:
-
Thank you for the update NeoPa. I was beginning to get a little worried that getting this query to work was a lost cause. Thank you though for delivering the message. I hope the roll out of the new site goes smoothly and doesn't present any major headaches.
Patiently waiting :)
Allen...Leave a comment:
-
Ahh Ok, so I've been tooling on the query all morning and I just cannot seem to get the joins right. So far I've broken the query down like so
[code=sql]
[Show Registration Names where they have a portfolio]) INNER JOIN
(((([Show only catagories where they have an id])
LEFT JOIN [Only show distributions that have a name])
INNER JOIN [show only investment names that have an id])
LEFT JOIN [show all deductions...Leave a comment:
-
Mary,
I've been looking over and over on the sql statement you wrote trying to understand it. I think I have a fairly good grasp on it and the (new to me) union select/query. I belive I understand the method used and why it was done the way it was. I'm trying to add this statement to the query though
[code=sql]
(((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*
(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100))...Leave a comment:
No activity results to display
Leave a comment: