Getting Count(*) From Left side of Join using WHERE on right side of join

[paulmac106@gmail.com]

Hi,

I have 2 tables: tblStatements and tblLines (one to many) Any
tblStatements record can have many associated records in tblLines.
The search criteria is against tblLines (ie tblLines.fldDat eofService

>= '6/1/06' and tblLinesfldDate ofService < '7/1/06'.) I join by tblStatements.f ldStatementPK and

tblLInes.fldSta tmentID (one to many)

I need to return a recordset that will look like (after i pass the
date range)

Total Statements Total Lines
136 1,123
24 869

the problem i get when i join the two tables is that i get this:

Total Statements Total Lines
1,123 1,123
869 869

any help or direction would be greatly appreciated.

[Roy Harvey]

Re: Getting Count(*) From Left side of Join using WHERE on right side of join

I think that COUNT(distinct tblStatements.f ldStatementPK) will do what
you want. This assumes a single-column PK for the Statements table.

Roy Harvey
Beacon Falls, CT

On 3 Jul 2006 05:17:43 -0700, paulmac106@gmai l.com wrote:

>Hi,
>
>I have 2 tables: tblStatements and tblLines (one to many) Any
>tblStatement s record can have many associated records in tblLines.
>The search criteria is against tblLines (ie tblLines.fldDat eofService

>>= '6/1/06' and tblLinesfldDate ofService < '7/1/06'.) I join by tblStatements.f ldStatementPK and

>tblLInes.fldSt atmentID (one to many)
>
>I need to return a recordset that will look like (after i pass the
>date range)
>
>Total Statements Total Lines
>136 1,123
>24 869
>
>the problem i get when i join the two tables is that i get this:
>
>Total Statements Total Lines
>1,123 1,123
>869 869
>
>any help or direction would be greatly appreciated.

[paulmac106@gmail.com]

Re: Getting Count(*) From Left side of Join using WHERE on right side of join

Roy,

Thank you so much. That worked perfectly!

thanks,
Paul