need limit query result...

[marlun]

Hi!
I have a query that selects everything from a table.
In the table I have a serialnumber(SN).
Looks like: 4-715-1025000901
Right now I have serialnumbers from 4-715-1025000901 to 4-715-1025001001.

And my result looks like:
SN Amount Price
4-715-1025000901 1 $100
4-715-1025000902 1 $100
4-715-1025000903 1 $100
...

I would like the query to select every serialnumber in the table with the first 8 similar characters: 4-715-10.
And write the info for all the products with the same first 8 characters on one line:

SN Amount Price
4-715-10 3 $300

I want this because I use a report to show whats in the table and now I get up about thousand lines instead of one.

Does anyone know how to do this. Is it in vb or sql??

Please help

 

[koresnordic]

Create a new query based on your table. Make it a summary query by clicking on the abstract E in the toolbar. create anew field e.g. "check:=left([sn],8)" to take the leftmost 8 chars. Group by this. Add the amount and price fields to the query and set these to "sum". This will give you what you want. Produce the report based on this query



Graham