VB Crystal Report, Still Having a Problem Counting a String Field

I have asked for help on this item in the past but I am still having trouble
getting it to work.

To review it, I have a database (SQL) connecting through ODBC on CR 11.
Currently there are 8 records in it (because I wanted a number I could work
with while building the report). 4 are label as 'Prenatal' and 4 are
'Infant' in the table Screenings under the field Screen_Type.

I need to Count the numer of each type I have.

I have done it this way:

Created a Formula field called PrenatalCount and inserted this formula:

IF {Screenings.Screen_Type}= 'Prenatal'
then 1
else 0

Then did a SUM on that field. I did the same for the Infant side.

I am getting a sum of 208 for Prenatal and 218 for Infant.

Can anyone help me in this? What am I doing wrong??

John,

Whenever I have problems with expected record counts, I copy the SQL from
the report and execute it in Enterprise Manager or Query Analyzer and review
the results. This usually helps identify any bad joins. From what you are
describing, your recordset is 418 records instead of an expected 8, so I
would look into that first.

Rob

That is actually a good idea. Only problem is I do not have SQL locally..I
am connecting to an off-site data store and the code I wrote is the Crystal
Syntax. Can you offer any methods where I can deleiver to syntax to my DBA
and have him run it for me?

Under the Database menu, you should find "Show SQL Query". This will give
you the SQL that is being passed to the server.

I did have a SQL instance running on my SBS so I used that to check the code
and it ran fine, so the code is good. It did, however, return 418 records,
so the problem MUST be in the JOIN type I am assuming (JOINing is an area
where I have problems, as you may have noticed in previous posts). Before I
takle this, is my base assumption correct? In the SQL code it is listed as
an INNER JOIN twice, so should I switch the JOIN type to an OUTER JOIN?

Thanks for the Help

ohn, it really depends on your data requirements. Switching to an outer
join will not reduce the total number of records, it may actually increase.
If you are targeting 8 records, you need to filter your query some more to
whittle the records down. Usually, I experiment in EM to get what I need and
then duplicate it in Crystal. One thing to look into is using subqueries
instead of joins.

Rob


"JC Harris" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...

JC Harris < XXXX@XXXXX.COM > rambled:

You can try it, but usually OUTER joins produce more results than INNER
joins.

--

KB - MCNGP "silent thug" #26

Briscobar AT gmail DOT com

Sometimes, I feel that www.mcngp.com isn't treating me with the respect
I deserve.

You have joins in your SQL script which are returning more than one entry
for each of these data files.

Are you perhaps linking out to another file which has many entries for the
record....text? visits? something like that?

Iain