ACCESS FEED: Re: [MS_AccessPros] Record(n)

A.D., Hendra

I totally agree about the clutter starting in 2007. Although it is simple enough to hide the Pane and Ribbon for the user, the developer suffers.

If the Access development team had done a better job of making web forms we would have something very useful, but the rest is all fluff and confusion as far as I am concerned.

I still develop in 2003 only and will continue to do so as long as an MDE will run in future versions of Access.

After all, themes, round buttons and the like do nothing for database applications. And the loss of User Level Security is a pain. I realize it was dropped because it was not really "security" as any Access developer knows, but it did give an excellent way to confine navigation based on groups. It only forced us all to develop our own home-grown user groups.

Just my two cent rant.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> You are most welcome Hendra!
>
> It is seen that with Access 2003, development gets done much faster. Later versions (2007 onwards) suffer from excessive clutter infringing upon net available space.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: Agestha Hendra
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, April 25, 2012 20:12
> Subject: Bls: [MS_AccessPros] Record(n) - Record(n-1)
>
>
> A.D...
>
> Great sample...thanx again A.D.... :)
> By the way do you still use Access 2003 for all your Access applications..? (I still use 2003..)
> If yes,...why..?
> Best Regards
> Hendra
>
>
>
> ________________________________
> Dari: A.D. Tejpal <adtp@...>
> Kepada: MS_Access_Professionals@yahoogroups.com
> Dikirim: Rabu, 25 April 2012 13:36
> Judul: Re: [MS_AccessPros] Record(n) - Record(n-1)
>
>
>
> Hendra,
>
> You are most welcome!
>
> Sample db named Query_CompareCurrRecWithPrev, uploaded to Samples folder in Files section of this group, demonstrates two styles for getting the desired results (current record value minus previous record value).
>
> 1 - Direct Single Query Method:
>
> Q_Result_SingleQuery
> (Makes use of two instances of embedded query)
> ================================
> SELECT Q1.FValue, Q1.FValue-Nz(Q2.FValue, 0) AS Result
> FROM (SELECT T_A.FValue, (SELECT Count(*) FROM T_A AS T WHERE T.ID <= T_A.ID) AS RowNum FROM T_A) AS Q1 LEFT JOIN (SELECT T_A.FValue, (SELECT Count(*) FROM T_A AS T WHERE T.ID <= T_A.ID) AS RowNum FROM T_A) AS Q2 ON Q1.RowNum = Q2.RowNum + 1;
> ================================
>
> Note: This is in modification to the SQL posted earlier, as it is observed that embedded SQL needs insertion on both sides of the join, instead of referring to the alias pertaining to first embedment.
>
> 2 - Simplified two stage method:
>
> Q_Result_A (second stage query)
> (Makes use of first stage query Q_A)
> ================================
> SELECT Q_A.FValue, Q_A.FValue-Nz(Q.FValue, 0) AS Result
> FROM Q_A LEFT JOIN Q_A AS Q ON Q_A.RowNum = Q.RowNum + 1;
> ================================
>
> Q_A
> (First stage: Serves as source for Q_Result_A above)
> ================================
> SELECT T_A.FValue, (SELECT Count(*) FROM T_A AS T WHERE T.ID <= T_A.ID) AS RowNum
> FROM T_A;
> ================================
>
> Both the above styles involve sliding comparison based upon unequal outer self join. Such an arrangement provides consistent results even for data having duplicate values and / or fluctuating trend, as shown below for token data in sample db:
>
> FValue Result
> 1 1
> 5 4
> 8 3
> 8 0
> 4 -4
> 9 5
> ------------------
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: Agestha Hendra
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, April 25, 2012 03:14
> Subject: Bls: [MS_AccessPros] Record(n) - Record(n-1)
>
> Thank you A.D....
> I downloaded many mdb samples from your site, they really help me...thanx for the samples.
>
> My Sql knowledge is very low, would you direct me step by step about your reply..?
> I have made T_A table with ID and Value as Fields...i've tried to implement your Sql syntax but always give erorr...
> Sorry if my english is not good...
> Regards
> Hendra
>
> ________________________________
> Dari: A.D. Tejpal <adtp@...>
> Kepada: MS_Access_Professionals@yahoogroups.com
> Dikirim: Selasa, 24 April 2012 20:44
> Judul: Re: [MS_AccessPros] Record(n) - Record(n-1)
>
>
> Hendra,
>
> If there is any likelihood of repeat values or if the sequence of values is likely to fluctuate between ascending and descending style, the following alternative query (Q_Result), involving sliding comparison based upon outer join, could be considered for universally consistent results. It makes use of primary key for deriving row numbers:
>
> Q_Result
> ===========================
> SELECT Q1.FValue, Q1.FValue-Nz(Q2.FValue, 0) AS Result
> FROM (SELECT T_A.FValue, (SELECT Count(*) FROM T_A AS T WHERE T.ID <= T_A.ID) AS RowNum FROM T_A) AS Q1 LEFT JOIN Q1 AS Q2 ON Q1.RowNum = Q2.RowNum + 1;
> ===========================
>
> T_A is the source table having fields ID (primary key) and FValue.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: John Viescas
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, April 23, 2012 22:16
> Subject: RE: [MS_AccessPros] Record(n) - Record(n-1)
>
> Hendra-
>
> Ah, it doesn't like the embedded UNION query. I got it to work by creating a table called MyTable and loading these values:
>
> Value
> 1
> 5
> 8
> 10
>
> Rather than embed the UNION query, I first had to create a query called T2:
>
> SELECT 0 As [Value] FROM MyTable
> UNION SELECT [Value] FROM MyTable;
>
> And the solution is:
>
> SELECT T1.[Value], (T1.[Value] - NZ(T2.[Value], 0)) As Result
> FROM MyTable As T1, T2
> WHERE T2.[Value] =
> (SELECT Max([Value]) FROM T2
> WHERE T2.[Value] < T1.[Value]);
>
> Value Result
> 1 1
> 5 4
> 8 3
> 10 2
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Villefranche-sur-mer, France)
>
> -------------------------------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Agestha Hendra
> Sent: Monday, April 23, 2012 4:29 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Bls: [MS_AccessPros] Record(n) - Record(n-1)
>
> Thank you John...
> I have tried your sql statement, here what i did :
> - Create a table and name it "T1"
> - Create a field in T1 Table, and name it "Value"
> - Create Query in Design View and then paste the sql syntax that you have given
>
> it gives erorr like "erorr syntax ...." everytime i run the query, i also have renamed the table to MyTable
> but it still the same erorr...please direct me again...
> Regards
> Hendra
>
> ________________________________
> Dari: John Viescas <JohnV@...>
> Kepada: MS_Access_Professionals@yahoogroups.com
> Dikirim: Senin, 23 April 2012 15:04
> Judul: RE: [MS_AccessPros] Record(n) - Record(n-1)
>
> Hendra-
>
> That won't display in the GUI. The query gets the cartesian product of your table and your table with a 0 value row added. I then selects for each row in the first copy of the table the row from the second copy whose value is the largest value that is smaller than the current value. It then subtracts the two values to give you the result you wanted.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Villefranche-sur-mer, France)
>
> -------------------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Agestha Hendra
> Sent: Sunday, April 22, 2012 11:42 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Bls: [MS_AccessPros] Record(n) - Record(n-1)
>
> Thanx John...
>
> It's hard to me to understand SQL Language without GUI .. Lol..
> Would you give me the snapshot of the query design of your example...?
>
> Regards
> Hendra
>
> ________________________________
> Dari: John Viescas <JohnV@...>
> Kepada: MS_Access_Professionals@yahoogroups.com
> Dikirim: Senin, 23 April 2012 3:48
> Judul: RE: [MS_AccessPros] Record(n) - Record(n-1)
>
> Hendra-
>
> Try this:
>
> SELECT T1.[Value], (T1.[Value] - NZ(T2.[Value], 0) As Result
> FROM MyTable As T1,
> (SELECT 0 As [Value] FROM MyTable
> UNION SELECT [Value] FROM MyTable) As T2
> WHERE T2.[Value] =
> (SELECT Max([Value]) FROM T2
> WHERE T2.[Value] < T1.[Value]);
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Villefranche-sur-mer, France)
>
> ----------------------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> agesthahendra@...
> Sent: Sunday, April 22, 2012 9:49 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Record(n) - Record(n-1)
>
> Hi everyone...
>
> Let say we have a field named Value,
> how can we calculate in query for the result to (Record(n))-(Record(n-1)),
> maybe it more simple to understand if i give an example like below :
>
> Value Result
> 1 1
> 5 4
> 8 3
> 10 2
>
> Results : (1)=1-(no record (n-1)), (4)=5-1, (3)=8-5, (2)=10-8 ...
>
> Any explanation would be very appreciated...thenk you very much
>
> Regards
> Hendra
>
> [Non-text portions of this message have been removed]
>