Count one value based on another value...

[caman]

Hello.

I do have, in my db, a single table.

I have the fields:

"ID"

"Productnumber"

"Location"

"In_Stock"

"Total_Locations"

My question is;

I have several productnumbers, both with and without letters/ numbers, but they are not unique.

Each Productnumber is given a location.

Each location is not unique to that productnumber.

But, I might have the same productnumber several times, but with different locations.

I need the field "Total_Locations" to count the number of unique locations, for each Productnumber.

I.E. I have:

PO# "920-580" on both Location "H1" and "291504".

The "Total_Locations" would be 2.

PO# "MayOS" is located only on Location "VM2".

The "Total_Locations" would be 1.

You get my drift?

Regards

Christian

[Breezer]

Assuming the PO# is in the "Productnumber" field and the ID is a unique not empty field, you could create a self relationship based on "Productnumber"

Then, make the field "Total_Locations" a calculation such as Total_Location = count(Location_self::ID)

Edited April 22, 2009 by Guest
self is reserved

[caman]

Perfect!

Thank you.

Christian