Explain SQL

[Jeanette]

Hi everyone can someone explain to me in english what the following sql is doing step by step:

SELECT OD.OrderDetailID, OD.OrderID, OD.OrderDate, nz([orderdate]-(SELECT max(OrderDate) FROM tblOrderDetails
WHERE OrderID=OD.OrderID and OrderDetailID < OD.OrderDetailID),0) AS DaysBetween
FROM tblOrderDetails AS OD;

I am using this as a base for a calculated field I am creating in my database. However, the results returned are not always correct. I am hoping that if I can understand what the above sql is doing, I can figure out how to fix my problem. Below is an sql view of what I have created. What is in bold is based on the above sql.

SELECT Project.PrjtID, Project.ProjectName, CityFunds.TypeFundID, ProjectActualsProjections.FYDate, Project.LoanNo, CityFunds.AmtReg, CityFunds.LoanType
FROM Project INNER JOIN (ProjectActualsProjections INNER JOIN CityFunds ON ProjectActualsProjections.FYID = CityFunds.FYID) ON Project.PrjtID = ProjectActualsProjections.PrjtID
ORDER BY Project.ProjectName, CityFunds.TypeFundID, ProjectActualsProjections.FYDate DESC;

SELECT LA.PrjtID, LA.TypeFundID, LA.ProjectName, LA.LoanNo, LA.LoanType, LA.AmtReg, LA.FYDate, CCur(nz([AmtReg]-(SELECT max(AmtReg) FROM LoanAmounts
WHERE TypeFundID=LA.TypeFundID and PrjtID=LA.PrjtID and LoanType=LA.LoanType and FYDate < LA.FYDate),0)) AS RunningBalance
FROM LoanAmounts AS LA;

This is my fisrt attempt a using sql and I want to start using it more frequently. Thanks in advance.

 

[ajetrumpet]

this is a very tall order Jeanne. and I doubt you will get a response, unless someone has a lot of time. just the truth here...

 

[speakers_86]

Google is a great tool for this. Just search for "sql tutorial", or "Microsoft access sql tutorial". Also, if you are going to learn something new, remember KISS. Start off with overly simple stuff, and go from there. I would help you, but I'm not quite the person to teach sql. I can interpret a little, but I'm better off in the design window.

Keep
It
Simple
Stupid (Optional!)

 

[wiklendt]

SELECT OD.OrderDetailID, OD.OrderID, OD.OrderDate, nz([orderdate]-(SELECT max(OrderDate) FROM tblOrderDetails
WHERE OrderID=OD.OrderID and OrderDetailID < OD.OrderDetailID),0) AS DaysBetween
FROM tblOrderDetails AS OD;

firstly, i find that using query design view MUCH easier. you can toggle between design view and SQL view, so that if you need the SQL for VBA, you can copy and paste already-working SQL into your VBA.

secondly, your SQL code. i presume the bottom part you understand, seeing as you said you wrote it. so i supose you need the top SQL explained?

let's break it up a little (this might make it easier for you already to see what might be going on):

[COLOR=Red]SELECT
[/COLOR][INDENT][COLOR=Red]OD.OrderDetailID,
OD.OrderID,
OD.OrderDate,[/COLOR]

[COLOR=Blue]nz([orderdate]-([/COLOR]
[/INDENT][INDENT]     SELECT
           max(OrderDate) 
     FROM
           tblOrderDetails
     WHERE 
           OrderID=OD.OrderID 
             and 
           OrderDetailID < OD.OrderDetailID)
[COLOR=Blue],0) [/COLOR]
[/INDENT][INDENT][COLOR=Blue]AS[/COLOR]
[/INDENT][COLOR=Blue]         DaysBetween[/COLOR]

[COLOR=Red]FROM
[/COLOR][INDENT][COLOR=Red]tblOrderDetails AS OD;[/COLOR]
[/INDENT]

SELECT is chosing your fields. it is choosing them FROM the tblOrderDetails table and renaming it AS "OD". the last field in this SELECT statement is actually called "DaysBetween" and is made by calculating values from other fields, and wrapped in a null handler.

nz() is the null handler - it checks and handles any nulls you may have withint the parentheses. usually like nz([YourField],ValueIfNull), where it will return YourField, unless YourFIeld is null, then it will return ValueIfNull. in your SQL, it is returning "0" if the rest of the calculation inside turns out to be null.

the calculated field nz() is named as "DaysBetween".

if we pull apart the query inside the nz() (which is the part that could be null, there for needs to be wrapped in a null handler):

here, it is selecting the maximum from the field OrderDate from the table "tblOrderDetails" but only where the orderID is equal to the FIRST select statement orderID BUT ALSO the OrderDetailID from OD MUST be less than the OrderDetailID from tblOrderDetails (this is why the first select statement gave the tblOrderDetails an alias of OD, so that the calculations would know which bits were what.

suggestion: throw this into the SQL view of a new query - and then toggle to see design view, it may trigger some understanding. it looks to me like it's calculating how long ago each order was made, in relation to the last order (which begs the question WHY?)

any more questions?

oh, one thing i'm NOT SURE about - the MAX operand - it either returns the MAX record (i.e., the last record to be put into the table) OR the MAX value... not sure what goes on there. i've tried recently to do a max query, but haven't mastered it...

i guess the bit you were not sure about were either the nz() or the "AS OD" part... the table needed to be renamed (and therefore creates a temporary instance of it) so that it doesn't clash with the OTHER calculations you are making from the SAME table... at least, that's mu understanding of how that works.

 

[wiklendt]

( you can even copy/paste the SELET statement from inside the nz() into a new query and see what it actually is selecting...) cool, eh?

 

[speakers_86]

Your so nice wiklen! I cant even give you any more rep points.

 

[WIS]

oh, one thing i'm NOT SURE about - the MAX operand - it either returns the MAX record (i.e., the last record to be put into the table) OR the MAX value

Max (Like First,Last, Min etc) refers to a fld in the SQL (qry).

Max(MyDate) is the latest date, Min(MyNumber) is the lowest no. etc

 

[Jeanette]

Hi everyone thanks for the help. Wilklendt your explanation has given me a better understanding of what is happening. Thanks again.

 

[wiklendt]

Your so nice wiklen! I cant even give you any more rep points.

wow! i'm just glad i can help.

(it's the thought that counts, speakers, LOL).

 

[wiklendt]

Max (Like First,Last, Min etc) refers to a fld in the SQL (qry).

Max(MyDate) is the latest date, Min(MyNumber) is the lowest no. etc

that's what i thought it was - i must be doing something wrong. anyway, no time to play with that now! gotta get my house ready for ANZAC day! not because it's ANZAC day, but because it's also my brother's 30th, and we've offered to host the party here

have a great day everyone!