Thanks theDBguy, it doesn't work. Because ProA and NewA have different serial and product name (but they do have some relation). they can not sum such easily.Hi. Have you tried using a Totals query?
Code:SELECT Serial, Product, Sum(Amount) As Total FROM TableName GROUP BY Serial, Product
does it means another table to keep such information? if do so, i have to creat a new table, and insert new data manually. Any method to avoid manual work?This is basically a translation problem. You have to translate the product name or number to another name or number. You have to decide which one is the preferred answer and then use a JOIN query to look up the alternate name. You can then do a GROUP BY to keep like products together.
if right([serial],3) are same, and right([product],1) are same also, will be regarded as a same product.How's a person or computer to know what serial/product values are the "same"? Have a table to sort it out?
it doesn't work, the file attached. it would be nice if you can take a look.SELECT Serial,
Dlookup("Product","yourTable","Serial = '" & [Serial] & "' And Nz([Remark]) <> 'New'") As Prod ,
Sum([Amount]) As Total
FROM yourTable
GROUP BY Serial
Exactly what I need! Thank you so much!see Table Query
yes, of course. Actually the serial and the product name are from other parties, I have no right to define them. That's why cause troubles.you should probably use an easy approach of creating Serial number (for New).
say only the first character is replaced with "X" (or "N") and the rest of digit is same,
example:
900367 ProdA
X00367 New
or create a Master table, example (tblAlternative)
AltSerial---------Serial-------Product
900367----------900367-----ProdA
388367----------900367-----ProdA
you can then Join this table to your
select tblAlternative.Serial, tblAlternative.Product, Sum(
.[Amount]) From tblAlternative
Left Join
On tblAlternative.AltSerial =
.Serial
Group by tblAlternative.Serial, tblAlternative.Product;