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"


.

Relevant Pages

  • Re: Expression Builder
    ... Open the query in Design View. ... This is what the SQL statement shows: ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Expression Builder
    ... Open the query in Design View. ... This is what the SQL statement shows: ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Expression Builder
    ... Open the query in Design View. ... Could you show us the sql statement for your query? ... This email account is my spam trap so ...
    (microsoft.public.access.queries)
  • Re: Expression Builder
    ... Could you show us the sql statement for your query? ... This will display the sql statement being generated by your actions in the ... This email account is my spam trap so I ...
    (microsoft.public.access.queries)
  • Re: How to create an AutoNumber field with a SQL statement?
    ... only, i.e., via SQL statement, not via Access GUI, and I'm having ... trouble defining an AutoNumber field. ... I did find that you can do it with the CreateTable method of the DAO.Database object with an Attribute parameter. ...
    (comp.databases.ms-access)