Hi,

I have a problem which I know will be easily solved but just cannot work out what Im doing wrong.

I have two tables. Clients and Episodes.

These tables are related via a field called "ClientID".

The Episodes table contains date fields called "Opened" and "Closed"

If a record has a date in the "Opened" field but not the "Closed" field, then the status of that episode is considered opened.

From the Clients table, I want to be able to determine how many records in the Episodes table have that status of open for that client. As I say, it is a simple thing but I'm doing something fundamentally wrong.

Do I need to setup a different relationship, or Calculation field in Clients?

Any help is appreciated.

Cheers,

Greg

Assuming no episode is closed without being opened first, try =

Count ( Episodes::Opened ) - Count ( Episodes::Closed ) 

Hi Comment.

Thanks for the reply.

I can see what that does, but what of the relationship between the two tables? Do I need to do something different?

No, this should work over your existing relationship.

Comment.

I eat my last post - I shouldnt have questioned you...

It gave me the information I need to resolve my query.

Many thanks... yet again. :-)

I shouldnt have questioned you...

Always question everything. :cool: