MS Access FIRST(), VFP = ???? 1

[vaxman9]

Hi all,

I am working with an ACCESS database that has many queries that make extensive use of the FIRST() function.
Not being an ACCESS person and with limited SQL experience, I was wondering if someone could enlighten me as to:
[ol][li]Is FIRST() a supported SQL command or is it unique to ACCESS?[/li]
[li]Is there some equivalent VFP function?[/li]
[/ol]
Thanks,
msc

 

[ramani]

Hi

When you ask, explain a bit about what you are asking, since people in this forum may not know access.

SO what is FIRST() ?

Anyway, are you looking for
GO TOP meaning to go to the first record of the table as per the index order set.

This can be achieved by the command
LOCATE
also.


ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[vaxman9]

Hi Ramani,

>> When you ask, explain a bit about what you are asking, since people in this forum may not know access.
>>
I understand that point. I am looking for the folks that have some ACCESS background. I would expect those without to be as clueless as I am concerning this function
>>
>>SO what is FIRST() ?

Well, that is what I thought I was asking . I am not that well schooled in ACCESS, that's why I posted what I did.

FIRST() is a function being used in the ACCESS queries I am trying to reproduce in VFP. It is not well documented in ACCESS help (at least I can't find any specific topic in the index or by searching on FIRST()), that is why I came here. It is a function that seems to produce data much like the UNIQUE keyword - but again, I am not sure exactly and again, that's why I am asking.

It is contained in the SQL statement that ACCESS builds and that is why I was wondering if it is a SQL function or just a ACCESS extension.

Actual ACCESS SQL statment:
SELECT [IN Locations-Sorted].[Part Id],
First([IN Locations-Sorted].[Location Id]) AS [Max Location],
First([IN Locations-Sorted].[Qty On Hand]) AS Qty,
First([IN Locations-Sorted].Sequence) AS Seq,
First([IN Locations-Sorted].Zone) AS Zn
FROM [IN Locations-Sorted]
GROUP BY [IN Locations-Sorted].[Part Id];

I apologize if this is not the correct forum and the confusion,
msc

 

[Mike Lewis]

Vaxman,

I apologize if this is not the correct forum

No need to apologise. You might as well ask it here as in the Access forum.

My understanding is that FIRST() and LAST() return the value of a specified field in the first or last record in a record set.

There's no real VFP equivalent. I'd guess that in VFP, you would have to create an intermediate cursor, then GO TOP in that to retrieve the records.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[vaxman9]

Mike,

That's what I have ended up doing.

Do you know if FIRST(), LAST() are recognizable functions by other SQL engines? I am just curious ...

Thanks again for your help,

msc

 

[perryf]

Just a guess, but by looking at the sql statement, I would assume that MAX() and MIN() are the VFP equivalents.

PF

 

[Mike Lewis]

Vaxman,

No, I've never come across those functions in any other SQL implementation. Also, I think it's unlikely, because in geenral SQL has no concept of 'first' and 'last' record. It sees records as a set, rather than a sequence.

Perryf,

Good point. MIN() and MAX() are similar, but I don't think they would work here. The problem is that the code is retrieving several values from the first record. MIN() and MAX() can each only retrieve one value, and since the record set can only be ordered on one key at a time, you could not use these functions multiple times in the same SELECT.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[vaxman9]

Mike/PF,

Thanks for taking the time to respond.

I get a bit frustrated with ACCESS. Of course I am talking from very little experience with the program. It just seems so difficult to get anything done in the "wizardy" interface of the program - guess I am spoiled with the VFP interface .. love that command box!

Cheers!
msc

 

[perryf]

Since you are talking about retrieving values from a particular record, possibly a correlated sub-query would help. something like:

select code, descrip from mytable where recdate in (select max(rec_date) from mytable a where code = mytable.code)

pf

 

[wgcs]

It seems that in Access, FIRST() is an aggregate operator which only makes sense when there's a "GROUP BY" expression.

This may produce the right results:

SELECT [IN Locations-Sorted].[Part Id],
       [IN Locations-Sorted].[Location Id] AS [Max Location], 
       [IN Locations-Sorted].[Qty On Hand] AS Qty,
       [IN Locations-Sorted].Sequence AS Seq,
       [IN Locations-Sorted].Zone AS Zn
   FROM [IN Locations-Sorted]
   GROUP BY [IN Locations-Sorted].[Part Id]

In cursory testing, any non-aggregate fields are taken from the 'last' record encountered of that group. However, since, as stated, SQL has no recognized record order, the record they are taken from is NOT guaranteed at all.

I'd expect that the "first()" function, or the non-aggregate fields in VFP, are only useful when the user doesn't care What record these come from... only that they come from the "same" record.