Re: Still Having a Problem Counting a String Field
- From: "Rob Diamant" <rob@xxxxxxx>
- Date: Wed, 11 May 2005 08:03:12 -0500
John, 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" <fake@xxxxxxxxx> wrote in message
news:%23CPgvZbVFHA.2560@xxxxxxxxxxxxxxxxxxxxxxx
>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
>
>
>
>
> "Rob Diamant" <rob@xxxxxxx> wrote in message
> news:%231SkH3YVFHA.1200@xxxxxxxxxxxxxxxxxxxxxxx
>> Under the Database menu, you should find "Show SQL Query". This will give
>> you the SQL that is being passed to the server.
>>
>> "John Harris" <harris@xxxxxxxx> wrote in message
>> news:ufiMP1XVFHA.3280@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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?
>>>
>>> "Rob Diamant" <rob@xxxxxxx> wrote in message
>>> news:OeHTZhXVFHA.3636@xxxxxxxxxxxxxxxxxxxxxxx
>>>> 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
>>>>
>>>>
>>>> "John Harris" <harris@xxxxxxxx> wrote in message
>>>> news:%23ydkiPXVFHA.2420@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>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??
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
.
- References:
- Still Having a Problem Counting a String Field
- From: John Harris
- Re: Still Having a Problem Counting a String Field
- From: Rob Diamant
- Re: Still Having a Problem Counting a String Field
- From: John Harris
- Re: Still Having a Problem Counting a String Field
- From: Rob Diamant
- Re: Still Having a Problem Counting a String Field
- From: JC Harris
- Still Having a Problem Counting a String Field
- Prev by Date: Variable as record selection
- Next by Date: Re: Still Having a Problem Counting a String Field
- Previous by thread: Re: Still Having a Problem Counting a String Field
- Next by thread: Re: Still Having a Problem Counting a String Field
- Index(es):
Relevant Pages
|
