Solved A complicated query, anyone can help? Thanks a lot! (1 Viewer)

[Maplem]

A complicated query, anyone can help? Thanks a lot!

 

[theDBguy]

Hi. Have you tried using a Totals query?

SELECT Serial, Product, Sum(Amount) As Total
FROM TableName 
GROUP BY Serial, Product

 

[Maplem]

Hi. Have you tried using a Totals query?

SELECT Serial, Product, Sum(Amount) As Total
FROM TableName
GROUP BY Serial, Product

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.

 

[plog]

How's a person or computer to know what serial/product values are the "same"? Have a table to sort it out?

 

[The_Doc_Man]

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.

 

[Maplem]

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.

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?

 

[Maplem]

How's a person or computer to know what serial/product values are the "same"? Have a table to sort it out?

if right([serial],3) are same, and right([product],1) are same also, will be regarded as a same product.

 

[arnelgp]

SELECT Serial,
Dlookup("Product","yourTable","Serial = '" & [Serial] & "' And Nz([Remark]) <> 'New'") As Prod ,
Sum([Amount]) As Total
FROM yourTable
GROUP BY Serial

 

[Maplem]

SELECT Serial,
Dlookup("Product","yourTable","Serial = '" & [Serial] & "' And Nz([Remark]) <> 'New'") As Prod ,
Sum([Amount]) As Total
FROM yourTable
GROUP BY Serial

it doesn't work, the file attached. it would be nice if you can take a look.

 

[arnelgp]

it will not work because record 4 and 5 have different serial number?
what is your formula to include them? last 3 digit must be the same?

 

[arnelgp]

see Table Query

 

[Maplem]

see Table Query

Exactly what I need! Thank you so much!

 

[arnelgp]

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;

 

[Maplem]

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;

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.

 

[Pat Hartman]

When you store data from other applications, it is just that - data. It is not a PK. At best you might search on it. The only purpose for keeping it is to communicate with the other application. THEREFORE, use your OWN product code to group by since obviously the serial numbers will be different and so will prevent grouping.