Records have an ID#, and a PID#. I need a third field to show how many occurrences of the record's PID# there are in other records. A find won't do; I need the third field for my purposes.. Any suggestions? Thanks
The self-join needs to be related by the field you are wanting to count similars.
So if you want to count how many records contain the same PID# as the current record, then the relationship for the self join should be:
table::PIDfield = table 2::PIDfield
Not sure why you are using a global field here.
Then you should have a calc field from the context of table which ideally counts the primary keys in your self-join table like this:
Count ( table 2::IDfield )
I think ExecuteSQL is your friend on this one.
ExecuteSQL ( "
SELECT COUNT ( yourID#field) FROM
WHERE yourPID#field = ?" ;"" ; "" ; yourTABLE::yourPID#field
A self join could also be used if you didn't want to use SQL.
Table::PID = Table 2::PID
Count ( Table 2::PID )
Would then provide the same result as the suggested ExecuteSQL query.
Yup, or that too!
And if you didn't want to count the current record, put a -1 at the end of the eSQL calc.
Set up your self join so
Table::ID ≠ Table 2::ID
I can't get it right. The result returned is a "0" and I know the result should be two in the case I looked at.
Here's my calculation:
PartnerLink for import from VRForm::Partner1MemberID = Partner Link to COUNT pairings::Partner1MemberID=
Count ( Partner Link to COUNT pairings::Partner1MemberID )
The self-joined tables are related by a global field.
The "PIDCount" field in the layout is from the second table, but it doesn't work if it's from the parent table either.
Thank you, both
Retrieving data ...