Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access/ Selecting specific rows as criteria

Posted on 2007-07-26
21
Medium Priority
?
352 Views
Last Modified: 2008-01-09
I have a query in Access. There will be a maximum of 5 rows.

In criteria/field I would like to select the number from row1, in another criteria the number from row2, in another criteria/field the number from row3, in another criteria/field the number from row4, in another criteria/field the number from row5.

In other words there has to be a way to go to design view in a criteria, select my field and under criteria select a record in a designated row under a specific field.

Example:
IDNumber
100
200
233
400
545

IDNumber(Criteria) select the record in row 3. In this case that would be 233.
0
Comment
Question by:ouestque
  • 12
  • 7
  • 2
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573785

to select a particular record ID

select * from tableName
where IDnumber=100          'will select the top 1

select * from tableName
where IDnumber=400          'will select the fourth record

not really sure what you are after
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573786

to select a particular record ID

select * from tableName
where IDnumber=100          'will select the top 1

select * from tableName
where IDnumber=400          'will select the fourth record

not really sure what you are after
0
 

Author Comment

by:ouestque
ID: 19573787
It would also work if there was a way to select the highest number in on field,  then the second highest number in another, then the third highest fourth, fifth etc.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573788

to select a particular record ID

select * from tableName
where IDnumber=100          'will select the top 1

select * from tableName
where IDnumber=400          'will select the fourth record

not really sure what you are after
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573789

to select a particular record ID

select * from tableName
where IDnumber=100          'will select the top 1

select * from tableName
where IDnumber=400          'will select the fourth record

not really sure what you are after
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573798

sorry my browser went crazy......
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573814
this will select the top 1

select Top 1 recordID
from tableName
order by recordId
                 

this will select the 5th record

select Top 1 recordID
from tableName
order by recordId desc
0
 
LVL 11

Expert Comment

by:fanopoe
ID: 19573819
this should do it, but it requires that your records be in ascending order:

SELECT Max([IDNumber]) AS YourResult
FROM (SELECT top 3  [YourTable].IDNumber FROM YourTable) AS [Temp];


the select top 3 gets the first 3 records, then the Max([IDNumber]) picks the highest one which is in the third position


hth
0
 

Author Comment

by:ouestque
ID: 19573826
Well here is what I want. I have a query1 that displays data in 5 rows. I want to create another query that says. "Search for the IDNumber in Row2 in query1"

All IDNumbers are different, so If I was able to search my field by the highest number, then second highest etc. That would work also.

In other words, Access would say "organize the rest of my fields by third highest IDNumber (etc.)"
0
 
LVL 11

Expert Comment

by:fanopoe
ID: 19573827
too much coffee cap?
;)
0
 

Author Comment

by:ouestque
ID: 19573833
I will try the above, answers provided
0
 

Author Comment

by:ouestque
ID: 19573835
Yes! Lots of coffee!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573847

select top 1 idnumber
from query1
where idnumber not in(select top 1 from query1 order by idnumber)
order by idnumber
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573854
something wrong with my browser, have to fix this first...
0
 

Author Comment

by:ouestque
ID: 19573924
Maybe there is an easier way to explain it. I have 2 queries.
Query1: returns 5 IDNumbers

Here is what I would like Query2 to do for now.
Query2: Take the third highest IDNumber in Query1

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19573960
are the IDNumbers in ascending order from  the result of Query1? always?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19574079
another way is to include ranking of records in query1,
then just select the rank on your second query

select * from query1
where ranking=3
0
 

Author Comment

by:ouestque
ID: 19574244
That is a really good Idea. How do Include ranking of records?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19574306
can you post sample data.
and post the sql of your query1
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 19574574
here is an example using table employees from the northwind database

SELECT E.LastName, E.HireDate, (Select Count (*) from Employees Where [HireDate] < [E].[HireDate])+1 AS Seniority
FROM Employees AS E
ORDER BY E.HireDate;

this shows the seniority based on hire date
0
 

Author Comment

by:ouestque
ID: 19695337
Thanks Capricorn, I will get to this soon. Sorry it has taken so long to assign points.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question