count more than one

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

Hello - I am stuck - can you please help
I have two tables in a qry - but I want to get the only
the second match to come from the qry - In some cases
there is only one match - but in some cases there are 12 -
but all I want to do is extract the second --
(third,fourth,respectively) - I don't want a count of all
but I want the actual record when it is the second record
Please can someone assist me _ I am lost - thanks in
advance - Mary
 
As you've not posted an example of the SQL statement, let me give you a
generic concept of what you can use:

SELECT TOP 1 FieldName FROM TableName
WHERE FieldName NOT IN
(SELECT TOP 1 FieldName FROM TableName
ORDER BY FieldName ASC)
ORDER BY FieldName ASC;
 
Hello - I am stuck - can you please help
I have two tables in a qry - but I want to get the only
the second match to come from the qry - In some cases
there is only one match - but in some cases there are 12 -
but all I want to do is extract the second --
(third,fourth,respectively) - I don't want a count of all
but I want the actual record when it is the second record
Please can someone assist me _ I am lost - thanks in
advance - Mary
Click to expand...

If you're asking for "the second record in a table" you may be up the
creek. A Table HAS NO ORDER. It's like a "bucket" of data; there is no
"first record" or "second record".

Could you explain the nature of the data, and what constitutes the
"second record"? A Query can be sorted by a field or fields within the
table, and you can use a criterion to select it - but I'd need to see
more about the nature of the data to be more specific.
 
Ken - thanks It helps out a lot!
-----Original Message-----
As you've not posted an example of the SQL statement, let me give you a
generic concept of what you can use:

SELECT TOP 1 FieldName FROM TableName
WHERE FieldName NOT IN
(SELECT TOP 1 FieldName FROM TableName
ORDER BY FieldName ASC)
ORDER BY FieldName ASC;

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
Back
Top