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

[code=vb]
Dim rs As Recordset
' create a recordset
Set rs = Me.RecordsetClo ne
' 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.AbsolutePosi tion = 2
' create your loop here for more results
Me.txtName = rs!fldName
Me.txtAge = rs!fldAge
[/code]


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