Finding the last record in a table

How would you go about locating the last of one or many records for an
account in a table.

The table can have just one or many and the account identifier is in a memo
field.

The format of the account number is easy enough to find in that it will have
the syntax, for example:

account = 12345.00

I'm assuming I should create a logical field called last that I can then
populate with .t. for last.
I can open the table twice, scan (for not reviewed-explained later) , flag
as last upon finding a perform a loca for the field in the second, with a
continue. I can also create a reviewed field that will be set to .t. after
either being hit in the scan, or located.

That's as far as I get with it.

--
Thank you,
Louis

Altman
Wed Nov 26 17:07:15 CST 2003

Just call the RECCOUNT() and find out how many records are in there and that
will give you last the last record number
If you want to go to that record try GO RECCOUNT()


"louis sorbera" <louis_sorbera@msn.com> wrote in message
news:%231Isa%23GtDHA.2392@TK2MSFTNGP10.phx.gbl...
> How would you go about locating the last of one or many records for an
> account in a table.
>
> The table can have just one or many and the account identifier is in a
memo
> field.
>
> The format of the account number is easy enough to find in that it will
have
> the syntax, for example:
>
> account = 12345.00
>
> I'm assuming I should create a logical field called last that I can then
> populate with .t. for last.
> I can open the table twice, scan (for not reviewed-explained later) , flag
> as last upon finding a perform a loca for the field in the second, with a
> continue. I can also create a reviewed field that will be set to .t. after
> either being hit in the scan, or located.
>
> That's as far as I get with it.
>
> --
> Thank you,
> Louis
>
>

Trey
Wed Nov 26 17:26:42 CST 2003

since you're dealing with only one table, you can use the recno() function
in a select

select * from theTable ;
where recno() in ;
(select max(recno()) from theTable ;
where theMemoField like "%account = 12345.00%") ;
into cursor cuLastRecord

"louis sorbera" <louis_sorbera@msn.com> wrote in message
news:%231Isa%23GtDHA.2392@TK2MSFTNGP10.phx.gbl...
> How would you go about locating the last of one or many records for an
> account in a table.
>
> The table can have just one or many and the account identifier is in a
memo
> field.
>
> The format of the account number is easy enough to find in that it will
have
> the syntax, for example:
>
> account = 12345.00
>
> I'm assuming I should create a logical field called last that I can then
> populate with .t. for last.
> I can open the table twice, scan (for not reviewed-explained later) , flag
> as last upon finding a perform a loca for the field in the second, with a
> continue. I can also create a reviewed field that will be set to .t. after
> either being hit in the scan, or located.
>
> That's as far as I get with it.
>
> --
> Thank you,
> Louis
>
>

Cindy
Wed Nov 26 22:26:28 CST 2003

In news: %231Isa%23GtDHA.2392@TK2MSFTNGP10.phx.gbl,
louis sorbera <louis_sorbera@msn.com> wrote:
> How would you go about locating the last of one or many records for an
> account in a table.

Hi Louis,

Trey's idea of retrieving the record with an SQL Select statement is good if
you just want the values, but if you need to be on the record itself you
could use a descending index. I've used "cPatientID + DTOS(dVisitDate)
DESCENDING" as an index expression to easily find the patient's most recent
visit.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org www.cindywinegarden.com

Andrew
Thu Nov 27 10:05:10 CST 2003

Cindy Winegarden wrote:
> In news: %231Isa%23GtDHA.2392@TK2MSFTNGP10.phx.gbl,
> louis sorbera <louis_sorbera@msn.com> wrote:
>> How would you go about locating the last of one or many records for
>> an account in a table.
>
> Hi Louis,
>
> Trey's idea of retrieving the record with an SQL Select statement is
> good if you just want the values, but if you need to be on the record
> itself you could use a descending index. I've used "cPatientID +
> DTOS(dVisitDate) DESCENDING" as an index expression to easily find
> the patient's most recent visit.

I was going to post this but you've done it for me ;)

This allows me to ask another question though - what is the difference
between a descending index and ascending?
I only ever use ascending indexes but sometimes I use SET ORDER TO <index>
DESCENDING.

I have found that I must be very careful because once you set the order
descending it "remembers" that for the particular tag until you close the
table...

eg:

PRIVATE ALL LIKE l*
CREATE CURSOR testy (date D, time C(8), desc C(40))
INDEX ON DTOS(date)+time TAG chron
INDEX ON desc TAG desc

FOR m.lrecord=1 TO 5
INSERT INTO testy (date, time) VALUES (DATE(), TIME())
WAIT WINDOW 'Just waiting a bit...' TIMEOUT 1
NEXT m.lrecord

* when was the last record for a given date?
SET ORDER TO chron DESCENDING
LOCATE FOR date=DATE() && the last record is right here!

* let's assume we do something with descriptions
SET ORDER TO desc
* do whatever

* let's get it in chronological order for something else
SET ORDER TO chron && Oh no!! it's in =reverse= chronological
order because the last time I used this tag I used it descending!

* end example

Does that make sense? Is it a bug or feature? I am using FPW2.6a here..
I rarely set a descending order but if I do I try to remember to set it
ascending again immediately afterwards. The helpfile says that both
ASCENDING and DESCENDING keywords are optional, it also says that neither
changes the index file in any way. I'd kind of decided that if ommitted it
defaulted to ASCENDING but this is not true at all, it seems as if the
session remembers the last order in which a tag was used and keeps that if
nothing qualifies it in the SET ORDER command..

Best regards
Andrew Howell

Stefan
Wed Dec 03 06:51:36 CST 2003

Hi Andrew,

Yyour example in FoxPro 2.6 the last result is in a Browse is
"time" in descending order and pointer at the first row, right?
In Vfp8 it is ordered as expected, time ascending and pointer
at the bottom.


Regards
-Stefan

"Andrew Howell" <ajh@work> schrieb im Newsbeitrag
news:u4NgMBQtDHA.2136@TK2MSFTNGP10.phx.gbl...
> Cindy Winegarden wrote:
> > In news: %231Isa%23GtDHA.2392@TK2MSFTNGP10.phx.gbl,
> > louis sorbera <louis_sorbera@msn.com> wrote:
> >> How would you go about locating the last of one or many records for
> >> an account in a table.
> >
> > Hi Louis,
> >
> > Trey's idea of retrieving the record with an SQL Select statement is
> > good if you just want the values, but if you need to be on the record
> > itself you could use a descending index. I've used "cPatientID +
> > DTOS(dVisitDate) DESCENDING" as an index expression to easily find
> > the patient's most recent visit.
>
> I was going to post this but you've done it for me ;)
>
> This allows me to ask another question though - what is the difference
> between a descending index and ascending?
> I only ever use ascending indexes but sometimes I use SET ORDER TO <index>
> DESCENDING.
>
> I have found that I must be very careful because once you set the order
> descending it "remembers" that for the particular tag until you close the
> table...
>
> eg:
>
> PRIVATE ALL LIKE l*
> CREATE CURSOR testy (date D, time C(8), desc C(40))
> INDEX ON DTOS(date)+time TAG chron
> INDEX ON desc TAG desc
>
> FOR m.lrecord=1 TO 5
> INSERT INTO testy (date, time) VALUES (DATE(), TIME())
> WAIT WINDOW 'Just waiting a bit...' TIMEOUT 1
> NEXT m.lrecord
>
> * when was the last record for a given date?
> SET ORDER TO chron DESCENDING
> LOCATE FOR date=DATE() && the last record is right here!
>
> * let's assume we do something with descriptions
> SET ORDER TO desc
> * do whatever
>
> * let's get it in chronological order for something else
> SET ORDER TO chron && Oh no!! it's in =reverse= chronological
> order because the last time I used this tag I used it descending!
>
> * end example
>
> Does that make sense? Is it a bug or feature? I am using FPW2.6a here..
> I rarely set a descending order but if I do I try to remember to set it
> ascending again immediately afterwards. The helpfile says that both
> ASCENDING and DESCENDING keywords are optional, it also says that neither
> changes the index file in any way. I'd kind of decided that if ommitted it
> defaulted to ASCENDING but this is not true at all, it seems as if the
> session remembers the last order in which a tag was used and keeps that if
> nothing qualifies it in the SET ORDER command..
>
> Best regards
> Andrew Howell
>
>

Andrew
Wed Dec 03 07:22:19 CST 2003

Stefan Wuebbe wrote:
> Hi Andrew,
>
> Yyour example in FoxPro 2.6 the last result is in a Browse is
> "time" in descending order and pointer at the first row, right?

Right.

You can easily work with it either way, you just need to know the behaviour.
I guess someone decided it was a bug, not a feature and fixed it.
Like I said, I'd assumed that leaving out ASCENDING and DESCENDING defaulted
to an ascending order but it doesn't [in FPW2.6 anyway.] That caught me out
the first time it happened!

Also I'm still curious - any idea why we can create ascending and descending
indexes when we can change our minds with the SET ORDER command?

--
Thanks!
Andrew Howell

Stefan
Wed Dec 03 12:51:56 CST 2003


"Andrew Howell" <ajh@work> schrieb im Newsbeitrag
news:%23FZN8BauDHA.3144@tk2msftngp13.phx.gbl...
> Stefan Wuebbe wrote:
> > Hi Andrew,
> >
> > Yyour example in FoxPro 2.6 the last result is in a Browse is
> > "time" in descending order and pointer at the first row, right?
>
> Right.
>
> You can easily work with it either way, you just need to know the
behaviour.
> I guess someone decided it was a bug, not a feature and fixed it.

Ja, I think so too

> Like I said, I'd assumed that leaving out ASCENDING and DESCENDING
defaulted
> to an ascending order but it doesn't [in FPW2.6 anyway.] That caught me
out
> the first time it happened!
>
> Also I'm still curious - any idea why we can create ascending and
descending
> indexes when we can change our minds with the SET ORDER command?

Hm, no ... like you I use Ascending / Descending with
Set Order only or with Order By
Maybe just another feature to get the famous always-
more-than-one-way FoxPro effect.

-Stefan