Wow..thanx John, it works... :)
Regards
Hendra
________________________________
Dari: John Viescas <JohnV@msn.com>
Kepada: MS_Access_Professionals@yahoogroups.com
Dikirim: Senin, 23 April 2012 23:46
Judul: 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]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar