I am buiding a “select query” based off a table that holds NAVs. What I want to do is create a column showing the daily returns of the NAVs. What I do not know how to do is tell Access to divide the current NAV by the previous NAV. This is what it looks like so far: NAVReturn: [NAV]*[Factor]/[] How do I say previous NAV. I would greatly appreciate any help.
I acn think of some ways of doing that (I would use a sub select statement if I had to), but you shouldn’t really think about databases as if they were spreadsheets. While there may be ordering in the database relying on that isn’t good and will cause you nothing but trouble in your life. VB was made for problems like this…
If the value is in the same column i.e based on date you may have to create an additional query and join back to original table using a date offset and perform the calculation on that result set using the division expression. Another way to do it would be to create an additional column and use an update query to place that additional value in the corresponding field. Otherwise if the previosu NAV is in a seperate field a simple division should work. post the table structure as it is hard to say without seeing that.
vb would be way overkill for this & would make things overly complicated. Assume your table is named “tbl” and contains columns “date” and “nav”, the query would be something like this: SELECT t.date, t.nav, (select max(nav) from tbl where date < t.date) as yesterdaysnav, t.nav/(select max(nav) from tbl where date < t.date) as navoveryesterdaysnav FROM tbl as t
^ except that’s way bogus which is why you use VB. And VB can hardly be overkill for anything. Programming done in databases is a) Hard to debug b) Difficult to put into version control c) Made worse by scaling d) Difficult to scale e) Difficult to transport to new databases f) Hard to find g) Doesn’t work like the query above (max(Nav) could give you something 6 years prior) h) orders of magnitude slower than compiled code i) needs to be eliminated and rewritten when the purpose changes even slightly etc. etc. Edit: VB is overkill?! I can write VB code for that while tripping on LSD and watching porn flicks.
Actually I made a mistake (wrote out the sql on the fly without checking it): SELECT t.date, t.nav, (select nav from tbl where date = (select max(date) from tbl where date < t.date)) as yesterdaysnav , t.nav/(select nav from tbl where date = (select max(date) from tbl where date < t.date)) as navoveryesterdaysnav FROM tbl as t is closer to what you want (obviously my hypothetical table is very simple). I hardly see how a sql statement can be called “way bogus”. a) sql is no more difficult to debug than any other language. b) sql can be saved to a text file & put into any version control system. c&d) I have no idea what you mean by “made worse by scaling” or difficult to scale. e) ansi sql is highly portable between databases. f) dont know what you mean by “hard to find”. g) I fixed the statement, the 1st one had a simple logic error (just like any piece of code can have). The fixed statement gives you the correct data. h) Set operations are by far faster than procedural logic, even when that logic is in compiled. In this situation there are sub queries involved, which probably makes it a wash time wise (certainly not “orders of magnitude” slower) considering you would have to employ some form of loop structure in a procedural language to get the same result. i) so does vb (or any) code… Writing and compiling vb code is overkill when you can write a simple query in < 30 seconds. Procedural logic for this would involve something along the lines of 1) fetching the data from the database, 2) dumping the navs into an array, 3) looping through the array dividing each position x by position x-1, 4) dumping the results into excel or whatever for further manipulation/presentation. This is more labor intensive than a writing a simple query & exporting the results to excel. Certainly there are instances where vb (or whatever language you prefer) is a better solution for data manipulation as some things require a large amount of procedural logic that sql is not well suited for. This is not one of those.
mh7 Wrote: ------------------------------------------------------- > Actually I made a mistake (wrote out the sql on > the fly without checking it): > > SELECT t.date, t.nav, > (select nav from tbl where date = (select > max(date) from tbl where date < t.date)) as > yesterdaysnav > , t.nav/(select nav from tbl where date = (select > max(date) from tbl where date < t.date)) as > navoveryesterdaysnav > FROM tbl as t > > is closer to what you want (obviously my > hypothetical table is very simple). > > I hardly see how a sql statement can be called > “way bogus”. It wasn’t close to working. That makes it way bogus in my book. > a) sql is no more difficult to debug than any > other language. Languages aren’t what makes things difficult to debug; it’s the environments available to debug them. Access, for example, doesn’t even have something that looks anything like a SQL debugger. I have never seen a SQL debugger that is anything close to the VB debugger. > b) sql can be saved to a text file & put into any > version control system. Every been successful getting people to do that? Anyway it separates the SQL from the table so you only put part of the code in version control. > c&d) I have no idea what you mean by “made worse > by scaling” or difficult to scale. Look at your line above - its already pretty tough to read. Now add a perdiem management fee to it. Now accrue an incentive fee. No add limits to incentive fees due to maximum fee limits. You can do that in SQL and it will be a God-awful mess. The code will just add comment/line, comment/line, until forever. > e) ansi sql is highly portable between databases. Yeah and I’ve pulled my hair out porting other people’s “Ansi sql”. There is no question that there is a subset of sql that is completely portable. Nobody knows what it is and when they start usig a database they start programming in all the little idiosyncrasies of the SQL on that database. Then they upgrade from Access to some real database and there’s hell to pay. > f) dont know what you mean by “hard to find”. There are billions of places people put SQL code - in stored procedure, temporary code snippets, text files stored in version control, triggers, and a billion other places where they may or may not do something. > g) I fixed the statement, the 1st one had a simple > logic error (just like any piece of code can > have). The fixed statement gives you the correct > data. > h) Set operations are by far faster than > procedural logic, even when that logic is in > compiled. In this situation there are sub queries > involved, which probably makes it a wash time wise > (certainly not “orders of magnitude” slower) > considering you would have to employ some form of > loop structure in a procedural language to get the > same result. Nope - I can program this without looping through the data at all (as long as it’s sorted, which is a good use for a SQL statement) > i) so does vb (or any) code… You can write vastly more robust code in VB or delphi or C# than you can in SQL that is much, much more encompassing and generalizable than SQL code. > > Writing and compiling vb code is overkill when you > can write a simple query in < 30 seconds. > Procedural logic for this would involve something > along the lines of 1) fetching the data from the > database, 2) dumping the navs into an array, 3) > looping through the array dividing each position x > by position x-1, 4) dumping the results into excel > or whatever for further manipulation/presentation. > This is more labor intensive than a writing a > simple query & exporting the results to excel. > If that’s your solution, it means you need to think about the problem better. > Certainly there are instances where vb (or > whatever language you prefer) is a better solution > for data manipulation as some things require a > large amount of procedural logic that sql is not > well suited for. This is not one of those. All I can say is that data manipulation and software can make or break your business and thinking about best practices is really important. All I can do is offer to help.
Thank all of you. I will try both. Greatly appreciated.
"It wasn’t close to working. That makes it way bogus in my book. " It was totally close to working, I made one minor error typing it out. According to your book every piece of code with a error is bogus (hope you arent using say…any microsoft products, because they are surely all “bogus”). "Languages aren’t what makes things difficult to debug; it’s the environments available to debug them. Access, for example, doesn’t even have something that looks anything like a SQL debugger. I have never seen a SQL debugger that is anything close to the VB debugger. " You don’t need a sql debugger (and yes, they do exist) to debug something trivial like this sql statement. Again, if you have more complex procedural logic, then you might want to have a more robust debugger. "Every been successful getting people to do that? Anyway it separates the SQL from the table so you only put part of the code in version control. " Yes I’ve been successful at it. The ddl from a table in a production environment should also be in scc, as well as any etl process that populates it. "Look at your line above - its already pretty tough to read. Now add a perdiem management fee to it. Now accrue an incentive fee. No add limits to incentive fees due to maximum fee limits. You can do that in SQL and it will be a God-awful mess. The code will just add comment/line, comment/line, until forever. " Two comments: 1) I would like to see your vb solution to the problem that is more compact than that sql statement. If its hard to read you just don’t know how to read it. 2) You are adding additional compexity by adding other data to be manipulated, which I have indicated several times may lead you to a different solution. I could arbitrarily introduce requirements that would make any vb solution to this problem useless as well…the solution has to run on a unix box because we like the results so much we are going to automatically post them on our company website every morning. Now you have to rewrite all your vb. As solutions to problems evolve (or I guess rather as problems evolve…), sometimes you have to do a little rework, thats totally natural. Starting with the easiest solution is also natural. "Yeah and I’ve pulled my hair out porting other people’s “Ansi sql”. There is no question that there is a subset of sql that is completely portable. Nobody knows what it is and when they start usig a database they start programming in all the little idiosyncrasies of the SQL on that database. Then they upgrade from Access to some real database and there’s hell to pay. " You can use the statement I just wrote in access, sql server, sybase or oracle. Try it yourself. That seems pretty portable to me. Are there differences between databases? sure. Again, for a simple task like this, it is simply faster & more efficient to use a simple sql statement - especially since you need to fish the data out of the database in the 1st place. "There are billions of places people put SQL code - in stored procedure, temporary code snippets, text files stored in version control, triggers, and a billion other places where they may or may not do something. " If you include places like between the cushions of my couch you could maybe get to a “billion” places to put sql code. Since you are unlikely to do that, there are actually only a few places to put it & if you standardize where you put it, you will actually be able to find it. You could also put your vb code in just as many places… "Nope - I can program this without looping through the data at all (as long as it’s sorted, which is a good use for a SQL statement) " I would very much like to see a program that can do this that does not use at least 1 looping structure somewhere (processing a vb recordset without some type of looping logic is going to be difficult). "You can write vastly more robust code in VB or delphi or C# than you can in SQL that is much, much more encompassing and generalizable than SQL code. " You can write code that you don’t have to change when the purpose that the code serves changes ? Amazing. Of course you can write more complex code using a full fledged programming language, that is so not the point…the point here is that you don’t have to write any code to begin with since you can just perform the task as you extract the data from the database (and you might as well since extracting the data is something that you have to do anyway). "If that’s your solution, it means you need to think about the problem better. " No, that is simply one solution that has all of the steps that need to happen for a procedural program to perform the task. Like any task there are many ways it can be accomplished…for this one though, those logical steps all need to happen. “All I can say is that data manipulation and software can make or break your business and thinking about best practices is really important. All I can do is offer to help.” So “best practices” according to you are to never use sql to do anything other than sort data & use vb for implementing anything else apparently…this is certainly not a “best practice” that I’ve ever seen observed. The bottom line is this is a task that can be accomplished simply using sql (something you have to do anyway) so writing a program in another language is overkill. Extending your logic, you should never use a sql order by clause when getting data out of a database, why bother when you can simply implement your favorite sorting algorithm and do it in vb. Now like I’ve said several times, I agree that you want to use vb in some instances when you have more complex tasks to perform…this is is simply not a complex task. Whats that old saying…if the only tool you have is a hammer everything begins to look like a nail.
this is like clash of the titans… i’m waiting for the mythical sea creature to appear.
This is one of the more interesting threads I’ve seen on here is awhile. Now then…anyone want a Doostang invite? (Disclaimer: I don’t know what Doostang is, but I’m assuming some sort of porn site)