How to Get Particular Records from ACCESS

[rupechamp]

The following SQL may work for you with the previous provisos :
[CODE=SQL]SELECT TOP 4 *
FROM Table001
WHERE [Name] Not In(
SELECT TOP 2 [Name]
FROM Table001)[/CODE]
SQL is a language rather than a package. MS Access includes it's own (Jet) SQL engine. Not to be confused with MS SQL Server which is a package (application).

Without further data to work with (IE an AutoNumber field) I cannot see that it's possible to do exactly what you want accurately.

Nice concept use of the sub-query by the way (from your post #12) ;)

Thanx Neo Pal,
I had tried this Also Assume a Condition When i have Only one name in All the Rows Then this Also fails.Here is my Query
[CODE=SQL]SELECT Top 3 Name from Table001 Where Name NOT IN(Select Top 3 Name from Table001) UNION SELECT NAME FROM (SELECT NAME,COUNT(*) FROM Table001 GROUP BY NAME HAVING COUNT(*) >1)[/CODE]
And
[CODE=SQL]select name from (select top 3 * from Table001 where name not in (select top 3 name from Table001) and name in (select top 6 name from Table001) ) union SELECT NAME FROM (SELECT NAME,COUNT(*) FROM Table001 GROUP BY NAME HAVING COUNT(*) >1) where name in (select top 6 name from Table001)[/CODE]
By Doing all of this Now i Observed That Apart from RecordSet there is no way to fetch the data between two rows .Means have to write Some code too.

Thanx

[FishVal]

Hi, all.

Actually there is a somewhat ugly workaround using VBA function with static variable incrementing on call and able to be reset at certain conditions. UNION query is used to reset the variable.

[code=vb]
Public Function DynAN(varDummy As Variant, blnReset As Boolean) As Long

Static lngAN As Long

If blnReset Then lngAN = -1
lngAN = lngAN + 1
DynAN = lngAN

End Function
[/code]

Query: qry1
[code=sql]
SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, False) AS AN
FROM tbl;
[/code]

Query: returns enumerated list
[code=sql]
SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
[/code]

Attaching sample ...

Ok. Attached.

Regards,
Fish

P.S. For further reference, this solution is a modification of that one adapted for table containing duplicate records.

[NeoPa]

That's what the "previous provisos" were all about.

In your scenario with the necessary fields simply not there you can approximate to a valid solution at best.

The GIGO law says you won't get much better out of it I'm afraid.

[mshmyob]

Nice Fish... Just changed the criteria in qryEnumeratedLi st to get to row 6.

cheers,

Hi, all.

Actually there is a somewhat ugly workaround using VBA function with static variable incrementing on call and able to be reset at certain conditions. UNION query is used to reset the variable.

[code=vb]
Public Function DynAN(varDummy As Variant, blnReset As Boolean) As Long

Static lngAN As Long

If blnReset Then lngAN = -1
lngAN = lngAN + 1
DynAN = lngAN

End Function
[/code]

Query: qry1
[code=sql]
SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, False) AS AN
FROM tbl;
[/code]

Query: returns enumerated list
[code=sql]
SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
[/code]

Attaching sample ...

Ok. Attached.

Regards,
Fish

P.S. For further reference, this solution is a modification of that one adapted for table containing duplicate records.

[rupechamp]

Thanx FishVal for your efforts .I really Appreciate That you all guy had spended your time for me. Anyway I checked your Query and it's Not Working Here . As i already mention that i am able to do this with the help of recordset but with query i don't know Because i don't have much idea on access.Here is my Sample Table on Which i had Checked your query on the basis of name

Name Description Value
Rupesh Hello 23
Ajay Hi 45
Rupesh Yes 60
Rupesh Okay 78
Rupesh Hello 23
Rupesh Hi 45
cc Yes 60

Thanx

Hi, all.

Actually there is a somewhat ugly workaround using VBA function with static variable incrementing on call and able to be reset at certain conditions. UNION query is used to reset the variable.

[code=vb]
Public Function DynAN(varDummy As Variant, blnReset As Boolean) As Long

Static lngAN As Long

If blnReset Then lngAN = -1
lngAN = lngAN + 1
DynAN = lngAN

End Function
[/code]

Query: qry1
[code=sql]
SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAg e, False) AS AN
FROM tbl;
[/code]

Query: returns enumerated list
[code=sql]
SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
[/code]

Attaching sample ...

Ok. Attached.

Regards,
Fish

P.S. For further reference, this solution is a modification of that one adapted for table containing duplicate records.