471,075 Members | 1,011 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

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

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.fldDateofService
>= '6/1/06' and tblLinesfldDateofService < '7/1/06'.) I join by tblStatements.fldStatementPK and
tblLInes.fldStatmentID (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.

Jul 3 '06 #1
2 1355
I think that COUNT(distinct tblStatements.fldStatementPK) 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, pa********@gmail.com wrote:
>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.fldDateofService
>>= '6/1/06' and tblLinesfldDateofService < '7/1/06'.) I join by tblStatements.fldStatementPK and
tblLInes.fldStatmentID (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.
Jul 3 '06 #2
Roy,

Thank you so much. That worked perfectly!

thanks,
Paul

Jul 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.