Re: Counting 'Many' in one-to-many realtionship

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Barley Man (BarleyMan_at_discussions.microsoft.com)
Date: 11/13/04 

Date: Fri, 12 Nov 2004 16:50:01 -0800

Thanks Dan. That was helpful and it WORKS even if it is very cumbersome. It
won't tolerate "Group by 1" so I changed it to 'group by members.fareno' and
that seems to have done the trick.

After Eugene's comment, I have to saythat, yet AGAIN, I find the manuals and
the online help completely USELESS! What inspires MS to be so HOPELESS with
Fox Manuals and training/example software? The manual for Fox 2.6 was GREAT
if not perfect. It ran us through a series of working examples that lead us
to a basic understanding. That COMPLETELY vanished as a concept the V3.

Thanks again

Ian

"Dan Freeman" wrote:

> You've got an embarrassment of riches. You could SET RELATION and get a
> count. You could use Sietse's seek() which is a shortcut.
>
> I'd do something like this:
>
> Select Count(*) ;
> From Family, Member ;
> Where family.farefno = member.farefno ;
> And family.fafeeam > 0 ;
> Group by 1
>
> Dan
>
> Barley Man wrote:
> > I have two tables in a relationship being 'family' and 'member'. They
> > are related by a common variable farefno in a one-to -many
> > relationship with a 'family' having zero, one or more 'members'.
> >
> > The 'family' pays the fees and the 'members' use the facilities. I
> > want to 'count' the 'members' where the 'family' have paid any fee
> > (family.fafeeam).
> >
> > For the LIFE of me, I can't remember how to do it. Can anyone give me
> > the code, please? I thought it was something like:-
> >
> > "Count for members where family.fafeeam>0"
> >
> > ..but I have forgotten to the command to 'link' the two tables.
> >
> > The whole thing would be great!
> >
> > Ian
> > TIA
>
>
>

Relevant Pages