Top 10 list



  • 1. linking subform master/child fields problem
    Hi, i have a form linked to a table. Because it is a very large form, i placed a tab control in it with sub-forms, that correspond to the same table. If i link them with the primary key (autonumber) i get an error on data entry. If i use other fields, they are not unique and the date entered is not stored. how can i solve my problem, so that everything is displayed well and all entries are stored? regards, Oli
  • 2. GROUP BY problem in access
    hi, i have a table( tblUpdate) with the following fields UpdateID, UpdateDate, ActionID UpdateID is the primary key ActionID is a foreign key i want to find the latest (by date) update of a particluar action. and i can do this using the following: SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.ActionID FROM tblUpdate GROUP BY tblUpdate.ActionID; this gives me : "MaxOfUpdateDate","ActionID" 24/9/2005 ,24 26/8/2005 ,25 11/9/2005 ,26 26/9/2005 ,28 which is good but i also need the primary key, UpdateID If i add updateID into the query i get SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.UpdateID, tblUpdate.ActionID FROM tblUpdate GROUP BY tblUpdate.UpdateID, tblUpdate.ActionID; i now get this "MaxOfUpdateDate","ActionID","UpdateID" 24/9/2005 ,24,23 22/9/2005 ,24,24 26/8/2005 ,25,25 26/9/2004 ,25,26 11/9/2005 ,26,27 26/9/2005 ,28,28 which is no good because im getting results for all the updateids i really just want : "MaxOfUpdateDate","ActionID" 24/9/2005 ,24 26/8/2005 ,25 11/9/2005 ,26 26/9/2005 ,28 but with the primary key for each one. any help would be much appreciated thanks in advance jim
  • 3. Validation Rules
    How do I set up the following Validation Rules in a table: 1. Two chars - both must be digits(0-9) 2. Three characters - first character must be a letter 3. 6 characters - all must be digits(0-9) 4. Up to 8 digits(0-9) 5. Up to 25 characters Thanks, Tom
  • 4. Use other value from table
    Hi I want a user to select a name (from a table) from a dropdown box but then use the corresponding email address for that person also stored in the users table in a colmn called email and store in a variable. I'm using a drop down box which gets its values from a table called users. Using SELECT [Users].[Name] FROM Users; I have got my form to email people when i click a button and fill out text boxes but i want the user to only see peoples names and not email addresses. Hope that makes sense. Thanks in advance.
  • 5. Linking form - Report
    I have a form called frm_quote and a report called rpt_quote. I want to be able to view the form on a particular quote number (field: QuoteNo - autonumber) and then go to the report where the the info on that particular quote no. is shown in the report. frm_quote is linked to query: qry_viewquote rpt_quote is linked to: qry_viewquoterpt In design of qry_viewquoterpt - I simply have the expression (for the field: QuoteNo ) [Enter Quote No]. This method does the job however I would like it to automatically recognize the quote number as described earlier without this manual input of the user. thanks.

Top 10 list

Postby chudson007 » Wed, 30 Jul 2003 17:05:07 GMT


I'm new to access and have two quick questions.

1) I've got a table with a couple of hundred thousand lines.
Each line contains a product number, quantity and price among other
things. How do I insert a field that multiplies the price by the

2) each product appears on several lines.  I want to pull out the top
ten products firstly by value (price * quantity) and then by frequency
(the sum of the quantities on each line)

Thanks in advance,


Re: Top 10 list

Postby Mike MacSween » Wed, 30 Jul 2003 21:21:08 GMT

You don't store data that can be calculated from other data. Run a query
against the table instead. I've assumed some table and field names:

SELECT tblSales.Product, tblSales.Price, tblSales.Quantity,
[Price]*[Quantity] AS [Value]
FROM tblSales;

Not quite sure whether you want 2 different things here. Anyway, this will
give you the top ten products by value, and how many of those top 10 you

SELECT TOP 10 tblSales.Product, Sum([Price]*[Quantity]) AS [Value],
Sum(tblSales.Quantity) AS [TotalSold]
FROM tblSales
GROUP BY tblSales.Product
ORDER BY Sum([Price]*[Quantity]) DESC;

HTH, Mike MacSween

Re: Top 10 list

Postby chudson007 » Thu, 31 Jul 2003 16:17:13 GMT

Cheers Mike, That worked perfectly.
Appreciate it.

Re: Top 10 list

Postby chudson007 » Thu, 07 Aug 2003 17:07:40 GMT

Here is my query

SELECT TOP 200 sales.Ean, Sum(sales.Qty) AS TotalSold,
Sum([sales].[Price]*[sales].[Qty]) AS [Value], sales.Name
FROM sales
GROUP BY sales.Ean, sales.Name
ORDER BY Sum([sales].[Price]*[sales].[Qty]) DESC;

It works fine, except that the result of Sales * Qty has no
consistency in how many decimal places it has.
How do I specify that the result should be to 2 decimal places.


Re: Top 10 list

Postby Mike MacSween » Wed, 13 Aug 2003 01:09:17 GMT


Similar Threads:

1.can you limit the amount of data in a Query (Top 10 list)

If I do a query that gives me 50 lines of data and I only want the top 10 
lines how would one do this?

2.Multiple "top 10" listing

Is it possible to have a query return the top 10 items in multiple
categories?  For instance, say I have a table with salesman id, product
category, customer, and price.  Can I get the top 10 customers per customer
or per category?  If I select the dropdown in the query design and put in
10, I just get 10 rows.


3.Top 10 Percent / Top 10 Values


I have a query, and within the query I want to limit the result set by 
adding criteria.

I have an if statement which says iif([debt-code]>100,True,False).  What I 
want to say is if the [debt-code] value is >100 then give me TOP 10 PERCENT 
or TOP 10 VALUES.  I have tried to add this to the if statement but it does 
not work.  Can anyone tell me how to do this in an if statement???



4.Selecting top 10 from a list

Is there an easy way to write a query that will select the 
top ten from a list. For example say you have a number of 
sales reps working for you and you want to know who were 
the top ten performers for the last month. All the data is 
loaded in a relevant table in an access database in which 
a query can be written to give an output with the 
following headings:

Month Rep_Name Sales_Revenue

5.How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first 10)

6. SELECT TOP 10 -- then get next 10?

7. Top 10 Home Based Businesses

8. Top 10 Worst Access Pick-Up Lines

Return to MS ACCESS


Who is online

Users browsing this forum: No registered users and 72 guest