Self-Join? Calculations?

[jwhetstone]

I have a database called versions. there is a job number field in this database. there may be many records with the same job number (different versions of a project). There is a field called sales status. This field can have one of two values - "Available" or "Sold." I'd like to be able to have a calculation field that would tell me how many versions with the same job number have been sold and thus have the value of "Sold" in the sales status field. i'm so far havin no luck. any suggestions?

[Ocean West]

Create these fields.

iOne = 1 (Stored, Nubmer)

JobStat = Job_Number & Status (Stored, Text)

JobSold = Job_Number & "Sold" (Stored, Text)

JobAvail = Job_Number & "Available" (Stored, Text)

Create 2 Self-relationships..

Sold = JobSold::JobStatus

Available = JobAvail::JobStatus

Create These Fields...

CountSold = Count(Sold::iOne)

CountAvail= Count(Available::iOne)

This will tell you how many Sold or Available you have per job number but not tell you how many "Different Versions" you have, you'd have to take it a step further and add the "version" number/letter to the indexes and create more relationships and count fields depending on the number of possible versions.

[ March 30, 2001: Message edited by: Ocean West ]

[jwhetstone]

OOoh! That worked like a charm. I never thought of combining both the job number and status fields into one field - thanks!

I've already got the number of versions for each project in a field. I have job number self-joined to itself. then I have a calculation field "Versions Sold" that is Count(Job Number::Version Name).

Thanks for the quick reply and help!

[ March 30, 2001: Message edited by: Jason Whetstone ]