Top 10 list

Hi,

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

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,

Ciar

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
sold:

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

Cheers Mike, That worked perfectly.
Appreciate it.
Ciar

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.

Ciar

Format()