Self-Join Relationship Question

[kseidule]

I have a layout which shows a home arrived on a lot, and then a number of items in a portal that the user clicks on having completed. The following is a simplified example of a layout...

Layout 1

Home: New Mercury Home Date Accepted: 12 Jan 2004

Done? Description

---------------------------------

() Yes () No () N/A Damage noted

() Yes () No () N/A Doors Locked

() Yes () No () N/A Hitch Attached

etc...

The portal items table and the home table are associated with the field KEY. I also have another layout which just shows the homes from the home table, as in...

Layout 2

Home: Date Accepted:

New Mercury Home 12 Jan 2004

New Uranus Home 12 Feb 2004

New Pluto Home 12 Mar 2004

What I want to do on Layout 2 above is add a COMPLETED field which shows which homes have had their items list completed. An item list would be considered to be completed when ALL items in the associated portal done column (radio buttons) are either set to "Yes" or "N/A". In other words, there are no "No" items in the portal list DONE field.

So the above list of homes would show as follows...

Home: Date Accepted: Completed:

New Mercury Home 12 Jan 2004 Y

New Uranus Home 12 Feb 2004 N

New Pluto Home 12 Mar 2004 Y

Pseudo-code wise, I want to say "If the associated portal list for the home contains no items set to "No" then the list has been completed, so set the COMPLETED field to "Y".

The tables are set up as follows...

Table:

t_Home_Arrival_Main

Fields:

KEY (Indexed, Auto-enter Serial)

DESCRIPTION (Text, description of home)

DATE_ACCEPTED (Date)

Table:

t_Home_Arrival_Items

Fields:

KEY

DESCRIPTION (Text, description of task)

DONE (Text, contains "Yes", "No, "N/A")

I hope that is enough explanation. I know there is some self-join table relationship and calc field magic I need to do here, but I just can't figure the bloody thing out. Any help, as always, is greatly appreciated.

Regards,

Kevin

[Chuck]

Create another field in your portal table called IsFinished. This will be a calculation field with a number result with the following calc:

DONE = "Yes" or DONE = "N/A"

Create a field in your home table called "AllDone" that is also a calculation field but with a text result:

Case(

  Count( t_Home_Arrival_Items::KEY ) = Sum( t_Home_Arrival_Items::IsFiniahed ),

  "Y",

  "N"

)

Chuck

[kseidule]

Holly Smokes!

Thanks Chuck. That worked just beautifully!

Many Regards,

Kevin