[Shoka]

I'm trying to manipulate some data at work but cannot manage to achieve what I want to do.

I have several records which contain the same(ish) data for example:

Product	QTY	Price
Milk	2	£0.50
Milk	1	£0.25
Bread	1	£0.50
Bread	3	£1.50

What I want to achieve is a new table with just 1 record per product but with the QTY and Price for the matching records summed.

Is it possible?

[jasonf01]

Im guessing you want to group the same product names together and total the quantity and price fields, you can do that in a query like:

SELECT Product, Sum(Qty) AS TotalQty, Sum(Price) AS TotalPrice FROM <TableName> GROUP BY Product;

...or another way of getting the same output would be to...
Go to the query sidetab click New
Add the table to the query
Click "Totals" (on the iconbar, its the Sigma button)
You then get an extra row (called "total") at the bottom
Double-click the Product field, Qty field and Price field so they appear at the bottom
They should all have a "total" calue of "Group By". Leave that for Product (as youre grouping by product code), change "total" for Qty and Price fields to "Sum" (add grouped items together).

You can then click "View" to see the contents of the query, or change it to a Make Table query to make a new table. I recommend saving this as a query as it will change with the products table, it helps you to keep your data in sync and will behave just like a table for most things you want to do with it (except you cant edit it).

Hope this helps,
Jas.

[Shoka]

Originally Posted by jasonf01

Im guessing you want to group the same product names together and total the quantity and price fields, you can do that in a query like:

SELECT Product, Sum(Qty) AS TotalQty, Sum(Price) AS TotalPrice FROM <TableName> GROUP BY Product;

...or another way of getting the same output would be to...
Go to the query sidetab click New
Add the table to the query
Click "Totals" (on the iconbar, its the Sigma button)
You then get an extra row (called "total") at the bottom
Double-click the Product field, Qty field and Price field so they appear at the bottom
They should all have a "total" calue of "Group By". Leave that for Product (as youre grouping by product code), change "total" for Qty and Price fields to "Sum" (add grouped items together).

You can then click "View" to see the contents of the query, or change it to a Make Table query to make a new table. I recommend saving this as a query as it will change with the products table, it helps you to keep your data in sync and will behave just like a table for most things you want to do with it (except you cant edit it).

Hope this helps,
Jas.

You are a legend - thanks a tonne! works perfectly and achieved exactly what I wanted to do.

[jasonf01]

Youre welcome, glad it worked for you.

On a side note, if anyone in Yorkshire is looking for an innovative developer with 18+ years of varied experience in software, hardware and interfaces, feel free to drop me a PM.

(shameless plug!)

Jas.