referring previous record of recordset from within sql statement

Hi,

i have a question. I am using Access 2000, ADO and ASP

select document_no, document_date, ledger_ac, debit, credit from
accounts

returns:

PV/1/06-07 10/14/06 Shripal Dalal A/c 10000
0
PV/1/06-07 10/14/06 Bank Of India A/c 0
10000
PV/2/06-07 12/14/06 Octroi A/c 5000
0
PV/2/06-07 12/14/06 Freight A/c 5000
0
PV/2/06-07 12/14/06 Petty Cash A/c 0
10000

I want this recordset to display as:

PV/1/06-07 10/14/06 Shripal Dalal A/c 10000
0
Bank Of India A/c
0 10000
PV/2/06-07 12/14/06 Octroi A/c 5000
0
Freight A/c
5000 0
Petty Cash A/c
0 10000

I can do this using arrays etc but can i use the intrinsic functions in
select statement to do this:

for eg:

select iif(lookuppreviousrecord(document_no) = document_no, '',
document_no) as document_no, document_date, ledger_ac, debit, credit
from accounts

I am already using the IIf statement for complex if then's and it works
like a charm. I just dont know what to use to lookup the previous
record in the current result set. Is this even possible ?

Thanks.

Regards,
Shripal.

Re: referring previous record of recordset from within sql statement

shripaldalal wrote:
> Hi,
>
> i have a question. I am using Access 2000, ADO and ASP
>
> select document_no, document_date, ledger_ac, debit, credit from
> accounts
>
> returns:
>
> PV/1/06-07 10/14/06 Shripal Dalal A/c 10000
> 0
> PV/1/06-07 10/14/06 Bank Of India A/c 0
> 10000
> PV/2/06-07 12/14/06 Octroi A/c 5000
> 0
> PV/2/06-07 12/14/06 Freight A/c 5000
> 0
> PV/2/06-07 12/14/06 Petty Cash A/c 0
> 10000
>
> I want this recordset to display as:
>
> PV/1/06-07 10/14/06 Shripal Dalal A/c 10000
> 0
> Bank Of India A/c
> 0 10000
> PV/2/06-07 12/14/06 Octroi A/c 5000
> 0
> Freight A/c
> 5000 0
> Petty Cash A/c
> 0 10000
>
> I can do this using arrays etc but can i use the intrinsic functions
> in select statement to do this:
>
> for eg:
>
> select iif(lookuppreviousrecord(document_no) = document_no, '',
> document_no) as document_no, document_date, ledger_ac, debit, credit
> from accounts
>
> I am already using the IIf statement for complex if then's and it
> works like a charm. I just dont know what to use to lookup the
> previous record in the current result set. Is this even possible ?
>

1. In a relational database, there is no such concepts as "previous record".
By definition, a table is an unordered set of rows. "Previous" only has
meanung when an order is applied to the rows. So you need to clarify what
you mean by "previous record". Your example query has no ORDER BY clause, so
you cannot depend on the results being returned in any particular order.

2. I don't understand the resultset you want. The formatting is confusing
the issue. I cannot tell where a record ends and where a new record begins.
Please clarify what you want. I suspect what you want can be achieved with a
grouping query, but I am not sure.



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: referring previous record of recordset from within sql statement

Hi,

sorry the formatting is indeed confusing the entire issue

look for eg: let us say i do "select document_no, ledger_ac from
accounts"

it will display as:

Document_no Ledger A/c

JV/1 shripal dalal a/c
JV/1 Bank of india a/c
JV/2 Bank of Saurashtra a/c
JV/2 Cash in Hand A/c

I want it do display as:

Document_no Ledger A/c
JV/1 shripal dalal a/c
Bank of india a/c
JV/2 Bank of saurashtra a/c
Cash in hand a/c

you may notice that if the previous document_no is the same as the
current one then I do not want the current document_no to display
again. (Two records or more constitute one transactions here, since
they are part of accounts transaction).

So is there a way to refer the previous record....something like

select iif(previousrecord(document_no) = document_no, '', document_no),
ledger_ac from accounts order by document_no

I use iif function extensively and it works like a charm.

thanks.

regards,
shripal.

Re: referring previous record of recordset from within sql statement

"shripaldalal" wrote in message
news:1160894478.168629.53810@h48g2000cwc.googlegroups.com...
> Hi,
>
> sorry the formatting is indeed confusing the entire issue
>
> look for eg: let us say i do "select document_no, ledger_ac from
> accounts"
>
> it will display as:
>
> Document_no Ledger A/c
>
> JV/1 shripal dalal a/c
> JV/1 Bank of india a/c
> JV/2 Bank of Saurashtra a/c
> JV/2 Cash in Hand A/c
>
> I want it do display as:
>
> Document_no Ledger A/c
> JV/1 shripal dalal a/c
> Bank of india a/c
> JV/2 Bank of saurashtra a/c
> Cash in hand a/c
>
> you may notice that if the previous document_no is the same as the
> current one then I do not want the current document_no to display
> again. (Two records or more constitute one transactions here, since
> they are part of accounts transaction).
>
> So is there a way to refer the previous record....something like
>
> select iif(previousrecord(document_no) = document_no, '', document_no),
> ledger_ac from accounts order by document_no
>
> I use iif function extensively and it works like a charm.
>
> thanks.
>

Why not simply store the document_no field value in a variable. On each
loop iteration compare the variable value with the document_no field if the
values are the same don't output the field in your HTML otherwise write it
out and copy the value to the variable.


> regards,
> shripal.
>

Re: referring previous record of recordset from within sql statement

shripaldalal wrote:
> Hi,
>
> sorry the formatting is indeed confusing the entire issue
>
> look for eg: let us say i do "select document_no, ledger_ac from
> accounts"
>
> it will display as:
>
> Document_no Ledger A/c
>
> JV/1 shripal dalal a/c
> JV/1 Bank of india a/c
> JV/2 Bank of Saurashtra a/c
> JV/2 Cash in Hand A/c
>
> I want it do display as:
>
> Document_no Ledger A/c
> JV/1 shripal dalal a/c
> Bank of india a/c
> JV/2 Bank of saurashtra a/c
> Cash in hand a/c
>
> you may notice that if the previous document_no is the same as the
> current one then I do not want the current document_no to display
> again. (Two records or more constitute one transactions here, since
> they are part of accounts transaction).
>
> So is there a way to refer the previous record....something like
>
Again, without an ORDER BY clause, there is no such thing as a "previous
record". In your example above, for the JV/1 document, you show the shripal
ledger displaying before the Bank of india one. This leads to two questions:
1. Is it important that the shripal record be first?
2. If so, what data exists in the table that will allow us to force it to be
shown first via an ORDER BY clause?

If the answer to 1. is "No", then do what Anthony suggests and loop through
the data, keeping track of which document_no you are working with through
the use of a variable. if "Yes", then you need to provide the answer to the
second question.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: referring previous record of recordset from within sql statement

Hi,

basically i just want to avoid showing document_no again if the
previous record in the SQL RESULT recordset (the one we get after doing
the select statement) (not the database) matches the document_no of the
current record position in the recordset

So as anthony says it should go like:

dim docno
docno = ""
do until rs.eof
if docno = rs("document_no") then
response.wirte "
- " & rs("ledger_ac")
else
response.write rs("document_no") & " - " &
rs("ledger_ac")
docno = rs("document_no")
end if

rs.movenext
loop

now instead of doing all this cant this be doine in the sql statement
itself ??? the main purpose is to make the program smaller because
there are lots of such statements in the reports.... makin arrays and
such if then statements code blows up alarmingly.....

regards,
shripal.

Bob Barrows [MVP] wrote:
> shripaldalal wrote:
> > Hi,
> >
> > sorry the formatting is indeed confusing the entire issue
> >
> > look for eg: let us say i do "select document_no, ledger_ac from
> > accounts"
> >
> > it will display as:
> >
> > Document_no Ledger A/c
> >
> > JV/1 shripal dalal a/c
> > JV/1 Bank of india a/c
> > JV/2 Bank of Saurashtra a/c
> > JV/2 Cash in Hand A/c
> >
> > I want it do display as:
> >
> > Document_no Ledger A/c
> > JV/1 shripal dalal a/c
> > Bank of india a/c
> > JV/2 Bank of saurashtra a/c
> > Cash in hand a/c
> >
> > you may notice that if the previous document_no is the same as the
> > current one then I do not want the current document_no to display
> > again. (Two records or more constitute one transactions here, since
> > they are part of accounts transaction).
> >
> > So is there a way to refer the previous record....something like
> >
> Again, without an ORDER BY clause, there is no such thing as a "previous
> record". In your example above, for the JV/1 document, you show the shripal
> ledger displaying before the Bank of india one. This leads to two questions:
> 1. Is it important that the shripal record be first?
> 2. If so, what data exists in the table that will allow us to force it to be
> shown first via an ORDER BY clause?
>
> If the answer to 1. is "No", then do what Anthony suggests and loop through
> the data, keeping track of which document_no you are working with through
> the use of a variable. if "Yes", then you need to provide the answer to the
> second question.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Re: referring previous record of recordset from within sql statement

shripaldalal wrote:

>
> now instead of doing all this cant this be doine in the sql statement
> itself ???
Again, relational databases have no concept of "previous record" so there is
nothing built into sql to allow the "previous record" to be referenced.

> the main purpose is to make the program smaller because
> there are lots of such statements in the reports.... makin arrays and
> such if then statements code blows up alarmingly.....
>

If your table design permitted it*, it is remotely possible it could be done
by means of a self-join, but from the looks of your situation, it does not
appear to be possible. Even if it was, performance would likely be
atrocious, and you would simply be transferring the "alarming" code into an
"alarming" sql statement. This is a presentation issue that should be
handled by presentation code.

* Your table would need a field, or a combination of fields that would
enable the records to be ordered (ranked) with no ties.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: referring previous record of recordset from within sql statement

"shripaldalal" wrote in message
news:1160933578.374966.90760@f16g2000cwb.googlegroups.com...
> Hi,
>
> basically i just want to avoid showing document_no again if the
> previous record in the SQL RESULT recordset (the one we get after doing
> the select statement) (not the database) matches the document_no of the
> current record position in the recordset
>
> So as anthony says it should go like:
>
> dim docno
> docno = ""
> do until rs.eof
> if docno = rs("document_no") then
> response.wirte "
- " & rs("ledger_ac")
> else
> response.write rs("document_no") & " - " &
> rs("ledger_ac")
> docno = rs("document_no")
> end if
>
> rs.movenext
> loop
>
> now instead of doing all this cant this be doine in the sql statement
> itself ??? the main purpose is to make the program smaller because
> there are lots of such statements in the reports.... makin arrays and
> such if then statements code blows up alarmingly.....
>

Easy. Move the SQL from the pages to the database as saved queries. You'll
get the benefit of execution plans being pre-compiled as well as reducing
the code in your ASP pages.


--
Mike Brind

Re: referring previous record of recordset from within sql statement

shripaldalal wrote:
> now instead of doing all this cant this be doine in the sql statement
> itself ??? the main purpose is to make the program smaller because
> there are lots of such statements in the reports.... makin arrays and
> such if then statements code blows up alarmingly.....
>
I was intrigued so i started playing with this.

If you add an autonumber field to your table (call it doc_id and make it the
primary key; create a non-unique index on the document_no field as well),
you can do something like this:

Create a saved query called PriorDocuments with this sql:

Select [doc_id] + 1 As PriorDoc_id, document_no From accounts


Then use this sql statement to retrieve your records:

Select IIf([p].[document_no] Is Null,[c].[document_no],'') AS document_no,
document_date, ledger_ac, debit, credit
From accounts c left join PriorRecord p ON c.document_no= p.document_no
AND (c.doc_id= p.PriorDoc_id)

I only did this as an intellectual puzzle. I still believe this method
should not be used in production. However, it is your application ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"