How to Get Particular Records from ACCESS

[rupechamp]

Hello my database looks like

Name Age
Hello 21
Hi 25
Bye 27
Welcome 16
Say 19
XYZ 18

Now i want to Retrive All the record between Column 3 to column 6 How i can do that In Microsoft Access .Here Condition is I can't Add any new column to The database

[mshmyob]

Hello Rupechamp,

I only see 2 columns (Name & Age)

Answer your 2nd question you go into design mode in your table and add more columns.

cheers,

Hello my database looks like

Name Age
Hello 21
Hi 25
Bye 27
Welcome 16
Say 19
XYZ 18

Now i want to Retrive All the record between Column 3 to column 6 How i can do that In Microsoft Access .Here Condition is I can't Add any new column to The database

[Big X]

Hello my database looks like

Name Age
Hello 21
Hi 25
Bye 27
Welcome 16
Say 19
XYZ 18

Now i want to Retrive All the record between Column 3 to column 6 How i can do that In Microsoft Access .Here Condition is I can't Add any new column to The database

You will have to create a query. You have only listed two column names. If you supply us the column names you want and the table name, I can write you a sql statement that can retrieve the information you want.

You question isnt to precise either. Do you want all information in column 3 to 6(all records). Or do you want all the information from rows 3 to 6 .

[rupechamp]

Hello All,
new column means i don't want to add any new column in my database like column no etc. On the basis on these two column only i want to write a query which will get all the data between column 3 and 6 in microsoft Access that's all i have to Do.And i think it will same even we want to try for ll rows value ,instead of Field name we can use * but how to access particular row 3 ,4,5,6 etc

Thanx

[mshmyob]

You cannot write a Query to get the data for column 3 to 6 if you do not have a column 3 to 6.

If what you really want is a query to get the data for rows 3 to 6 then you need to tell us what the query is based on (ie: Peoples names - Names that start with A to C for example, or people whose age falls into a certain range)

Give us some idea of what you want to filter on.

cheers,

Hello All,
new column means i don't want to add any new column in my database like column no etc. On the basis on these two column only i want to write a query which will get all the data between column 3 and 6 in microsoft Access that's all i have to Do.And i think it will same even we want to try for ll rows value ,instead of Field name we can use * but how to access particular row 3 ,4,5,6 etc

Thanx

[rupechamp]

You cannot write a Query to get the data for column 3 to 6 if you do not have a column 3 to 6.

If what you really want is a query to get the data for rows 3 to 6 then you need to tell us what the query is based on (ie: Peoples names - Names that start with A to C for example, or people whose age falls into a certain range)

Give us some idea of what you want to filter on.

cheers,

My fault I am Sorry ,I mean to say from row 3 to row 6.There is Name which is Not alphabeticaly and age also not in any paricular order just want to get the value on the basis of row only

Thanx

[mshmyob]

It is very important to understand that Access does NOT use record numbers. The numbers you see at the bottom of the screen are not true record numbers but just pointers based on the sort order of the view you are currently on. If you change the sort order your so called record numbers (row numbers) will change.

The key point you must remember when using the code is that you MUST ALWAYS open the table in the EXACT same sort order all the time. Since ACCESS does not use record numbers if you open the table with a different sort order you will get different results.


The following code will show you how to extract data based on 'record numbers'. I am showing a single record (#3). You can create a loop to extract data up to whatever 'record number' you want. I am displaying the result to the form.

Assume the form is bound to your table and you have 2 text boxes called txtName and txtAge and a command button. Put the following code in the On Click event of the command button.

The 2 fields (columns) in your table are called fldName and fldAge

Dim rs As Recordset
' create a recordset
Set rs = Me.RecordsetClone
' populate the recordset - you must do this
    rs.MoveLast
    rs.MoveFirst
' end populating recordset
 
' move to the 3rd 'record number' (remember absolute position is 0 based
    rs.AbsolutePosition = 2
' create your loop here for more results
    Me.txtName = rs!fldName
    Me.txtAge = rs!fldAge
 

cheers,

My fault I am Sorry ,I mean to say from row 3 to row 6.There is Name which is Not alphabeticaly and age also not in any paricular order just want to get the value on the basis of row only

Thanx

[Big X]

Couldn't he just added a column with auto number ' RecordNum' then run a query.

Select *
From ['TABLENAME']
Where RecordNum IN(3,4,5,6) ;
 

[mshmyob]

Sounds good in theory but what happens when he starts deleteing records and the autonumbers start having gaps or he has a different sort, then the Access 'record numbers' won't match the RecordNum values you have in your table.

Note I put quotes around 'record number' since Access doesn't use record numbers and trying to use record numbers for anything in Access is frowned upon and is not reliable.

I don't know the OP's reason for wanting to do what he is asking but we have very limited information from him and I am trying to give him what he is asking for (regardless if I agree with it).

cheers,

Couldn't he just added a column with auto number ' RecordNum' then run a query.

Select *
From ['TABLENAME']
Where RecordNum IN(3,4,5,6) ;
 

[rupechamp]

Sounds good in theory but what happens when he starts deleteing records and the autonumbers start having gaps or he has a different sort, then the Access 'record numbers' won't match the RecordNum values you have in your table.

Note I put quotes around 'record number' since Access doesn't use record numbers and trying to use record numbers for anything in Access is frowned upon and is not reliable.

I don't know the OP's reason for wanting to do what he is asking but we have very limited information from him and I am trying to give him what he is asking for (regardless if I agree with it).

cheers,

Thanx , I know That i can Do it With The help of record Set But Don't want it to do at code level had already used this record no logic .Right Now my requirement is that can i do it itself a query Level. I can tell one thing .I had triwd where Not In clause it Works but it fails if i have duplicate value inside my database

Thanx

[mshmyob]

What is your primarky key and post your SQL for your query.

cheers,

Thanx , I know That i can Do it With The help of record Set But Don't want it to do at code level had already used this record no logic .Right Now my requirement is that can i do it itself a query Level. I can tell one thing .I had triwd where Not In clause it Works but it fails if i have duplicate value inside my database

Thanx

[rupechamp]

What is your primarky key and post your SQL for your query.

cheers,

Here is the query

SELECT TOP 3 * FROM Table001 WHERE Name not in (select distinct Name from (SELECT TOP 3 * FROM (SELECT * FROM Table001 )));

It Shows The Row value from 3 to 6 But it remove duplicate data due to Where clause . And in this there is no Primary key .it's Simply two Line table.And one more hing it's in Access not in SQL

Thanx

[mshmyob]

You can get it to work if you add another Column (make it an Autonumber) like Big X says and make it the PK and then change the field 'Name' in your query to the autonumber column and it will display even if Name is duplicated.

Note: Your ordering of the data will still give you different results if you change it.

The biggest problem you are having is that you have an improperly designed table.

cheers,

Here is the query

SELECT TOP 3 * FROM Table001 WHERE Name not in (select distinct Name from (SELECT TOP 3 * FROM (SELECT * FROM Table001 )));

It Shows The Row value from 3 to 6 But it remove duplicate data due to Where clause . And in this there is no Primary key .it's Simply two Line table.And one more hing it's in Access not in SQL

Thanx

[rupechamp]

You can get it to work if you add another Column (make it an Autonumber) like Big X says and make it the PK and then change the field 'Name' in your query to the autonumber column and it will display even if Name is duplicated.

Note: Your ordering of the data will still give you different results if you change it.

The biggest problem you are having is that you have an improperly designed table.

cheers,

Thanx for your Suggestion .So i can Assume that There is no Direct Solution in case of Access Even if i use AutoNumber Column and Deleted Some Rows from Middle of table Then Again Problem .Thanx for all your Suggestion and to Confirm my point . Yes i agrees that it's a bad designed table .But in this Senario i am helpless I can't do anything in this case have to Do everything By these column only .

Thanx

[NeoPa]

The following SQL may work for you with the previous provisos :

SELECT TOP 4 *
FROM Table001
WHERE [Name] Not In(
    SELECT TOP 2 [Name]
    FROM Table001)

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) ;)

[rupechamp]

The following SQL may work for you with the previous provisos :

SELECT TOP 4 *
FROM Table001
WHERE [Name] Not In(
    SELECT TOP 2 [Name]
    FROM Table001)

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

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)

And

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)

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.

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
 

Query: qry1

SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, False) AS AN
FROM tbl;
 

Query: returns enumerated list

SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
 

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 qryEnumeratedList 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.

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
 

Query: qry1

SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, False) AS AN
FROM tbl;
 

Query: returns enumerated list

SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
 

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.

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
 

Query: qry1

SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, True) AS AN
FROM tbl
UNION SELECT tbl.txtName, tbl.lngAge, DynAN(tbl.lngAge, False) AS AN
FROM tbl;
 

Query: returns enumerated list

SELECT qry1.*
FROM qry1
WHERE qry1.AN<>0 And qry1.AN>=3 And qry1.AN<=8
ORDER BY qry1.AN;
 

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.