Update Query Help

[mindbender]

overview: I have a table that has revenue associated with each serial number, so a serial number can be duplicated in the system. What I want to do is if the install revenue is >0 and the Order_code is either 12 or 06 then update the install_units to 1 on the first instance of the serial number.



I was able to isolate the serial numbers using a select statement but it uses totals to limit the list to 1 instance of the serial number
Order type - Group By
Serial Number - Group BY
Install revenue - MAX

Is there a way to have this be an update statement somehow even though it uses totals?

Thanks

Bret

[June7]

Ideally, aggregate data is not saved to tables but is calculated when needed.

I don't understand duplication of serial number.

What determines the 'first' instance of serial number - is there a date or a record identifier? Records in table do not have inherent order.

[mindbender]

Really all I want to do is put the number 1 in the Install Units field of the first instance of the serial number (if there is more than 1) where order type is either 02 or 16 and Install Revenue is >0

[June7]

Doesn't answer my question. What criteria can be used to identify the 'first' instance of each serial number? And why save to only the 'first' and why save aggregate data at all?

[mindbender]

I was just using MAX on Revenue to isolate one serial number if there were multiples. The reason why the data is only going on one instance of the serial number is because the install unit count is only 1 per serial number and putting a 1 in all the instances would throw the numbers off

[June7]

Do you have a master table of all serial numbers where the serial number is a unique value?

Max might not be helpful as unique identifier because each instance could have the same Revenue.

[mindbender]

not currently but I could create a table with a list of unique serial numbers

[June7]

That would be the easiest place to save the aggregate value.

[mindbender]

ok I will give that a try

Thanks for the help