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

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@airtelmail.in>
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@airtelmail.in>
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@msn.com>
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@msn.com>
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@ymail.com
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]