423,867 Members | 3,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,867 IT Pros & Developers. It's quick & easy.

Select TOP x number of records

P: 84
We have a query that calculates the percentage of customers we have in three groups. (Active, Inactive, Pending). The table we get the precentages from has over 1 million records. I need to design a query that will pull 100 records from a table using the percentages of each group. Ex. if the Active Group is 53% of the database then 53 of my 100 records needs to be from the Active group. Is there a way to pass the percentage onto the SELECT TOP n records? or is there another way to handle this?
Aug 7 '07 #1
Share this Question
Share on Google+
20 Replies


Rabbit
Expert Mod 10K+
P: 12,280
As far as I know, the x has to be a constant. I think you'll need to use VBA to rewrite the query definition before each run.
Aug 7 '07 #2

FishVal
Expert 2.5K+
P: 2,653
We have a query that calculates the percentage of customers we have in three groups. (Active, Inactive, Pending). The table we get the precentages from has over 1 million records. I need to design a query that will pull 100 records from a table using the percentages of each group. Ex. if the Active Group is 53% of the database then 53 of my 100 records needs to be from the Active group. Is there a way to pass the percentage onto the SELECT TOP n records? or is there another way to handle this?
Fortunately Access SQL supports the feature you want.
Plz read Access help topic "ALL, DISTINCT, DISTINCTROW, TOP Predicates".
Aug 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,280
Fortunately Access SQL supports the feature you want.
Plz read Access help topic "ALL, DISTINCT, DISTINCTROW, TOP Predicates".
I think though that it needs to be variable, and I don't think Access supports that.
Aug 7 '07 #4

FishVal
Expert 2.5K+
P: 2,653
I think though that it needs to be variable, and I don't think Access supports that.
Rabbit, I apologize, but you should see Access help topic concerning this.
SELECT TOP x PERCENT is the syntax. As far as I know it is a part of ANSI SQL.
Aug 7 '07 #5

Rabbit
Expert Mod 10K+
P: 12,280
Rabbit, I apologize, but you should see Access help topic concerning this.
SELECT TOP x PERCENT is the syntax. As far as I know it is a part of ANSI SQL.
No, I think the percent itself will change. At one time it could be 53% at others it could be 65%. And that this percent depends on the characteristics of the dataset rather than being a set percentage. At least that's my understanding of the OP's issue.
Aug 7 '07 #6

P: 84
Rabbit, I apologize, but you should see Access help topic concerning this.
SELECT TOP x PERCENT is the syntax. As far as I know it is a part of ANSI SQL.

Here is what I found in the help regarding the TOP x Percent

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

My question is how can I change the value of 10 without altering the sql each time. I was thinking I could use a variable that I could pass in. Something like this

SELECT TOP {Enter Value:] PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

'When launched the query would ask for the value. I need some control on the percentage
Aug 7 '07 #7

Rabbit
Expert Mod 10K+
P: 12,280
I don't think you can do that, you'll have to change the query def.
Aug 8 '07 #8

P: 84
Is there a way to run the Select statement in Visual Basic where I could use a variable for the percentage?
Aug 8 '07 #9

P: 7
Can u try something like this?...

SELECT TOP " & v & " PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

where v is your passed in variable.

Good Luck...
Aug 8 '07 #10

Rabbit
Expert Mod 10K+
P: 12,280
Is there a way to run the Select statement in Visual Basic where I could use a variable for the percentage?
Running a SELECT statement only doesn't do anything in VBA. It's not an action query so you won't see any results. You can, however, change the query def in VBA and open the query that way.
Aug 8 '07 #11

P: 84
Thanks everyone for the pointers. I was able to get the results I needed by making the VB statement an Update one. Using Dlookup I get my percentages and I pass the variable into the update statement which writes the results to a temporary table. Again thanks for the pointers.
Aug 18 '07 #12

Rabbit
Expert Mod 10K+
P: 12,280
That's one way.
The other way I was talking about is:

Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2. Set qdf = CurrentDb.QueryDefs("[Query Name]")
  3. qdf.SQL = "SELECT TOP " & SomeNumber & " PERCENT FROM tbl_Data;"
  4. Set qdf = Nothing
  5.  
Aug 18 '07 #13

P: 3
I have been looking for a way to do this. Can you please clear this up little more. I am trying to find the TOP N of a query. when I used you method I got an error 3141 Select statement includes a reserved word or argument name. I can not see anything wrong for the life of me. I would really apricate your help

Thanks

zeki

code:

Set dbparts = CurrentDb
Set qdf = dbparts.QueryDefs("qrymulticheckout")
qdf.SQL = "SELECT TOP " & 3 & " FROM tblcheckout; "
Aug 27 '07 #14

Rabbit
Expert Mod 10K+
P: 12,280
SomeNumber in my code is actually a variable holding a number that can be different at any time. If you're using a constant unchanging number then you don't need to separate it from the string.

My code also left out the fields to select. You need to include some fields for it to select. That's why you're getting the error.
Aug 27 '07 #15

P: 15
Hello every one. I am new to MS Access. I am using data SQL command SELECT to fetch some data from Access tables. I want to use DAO.Recordset object to get the data, but somehow my compiler is not recognizing the DAO object. I am using Access 2003. Can somebody help me. I cannot declare Dim rs As DAO. Thanks in advance
Aug 27 '07 #16

Rabbit
Expert Mod 10K+
P: 12,280
Hello every one. I am new to MS Access. I am using data SQL command SELECT to fetch some data from Access tables. I want to use DAO.Recordset object to get the data, but somehow my compiler is not recognizing the DAO object. I am using Access 2003. Can somebody help me. I cannot declare Dim rs As DAO. Thanks in advance
This is not related to this thread. Please post your question in a new thread.
Aug 27 '07 #17

P: 3
SomeNumber in my code is actually a variable holding a number that can be different at any time. If you're using a constant unchanging number then you don't need to separate it from the string.

My code also left out the fields to select. You need to include some fields for it to select. That's why you're getting the error.
Thanks for the quick reply. My original code had a variable. Because of my in experience i try to get them working with fixed values and then introduce the variables one by one. It is time consuming but I like because trouble shooting is much more easier.

I have added the fields to select but I still get the same error message. Thank you in advance for all the help.

Regards Zeki

New code

na = 3
Set dbparts = CurrentDb
Set qdf = dbparts.QueryDefs("qrymulticheckout")
qdf.SQL = "SELECT TOP " & na & " FROM tblcheckout.*, tblcheckout.quantity, tblcheckout.checkoutauto;"
Aug 28 '07 #18

Rabbit
Expert Mod 10K+
P: 12,280
Your SQL format is wrong.

It's SELECT FieldList FROM TableName;
Aug 28 '07 #19

P: 3
Thanks for the answer. i have got it working now.
Aug 28 '07 #20

Rabbit
Expert Mod 10K+
P: 12,280
Not a problem, good luck.
Aug 28 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.