Re: Still Having a Problem Counting a String Field

Tech-Archive recommends: Fix windows errors by optimizing your registry

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??
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Equivalent of Get Next Extended to retrieve only unique keys?
    ... The only real "extra" stuff is pertaining to SQL query optimization. ... Bill Bach wrote: ... Retaining the ability to filter on multiple>> items would probably ...
    (comp.databases.btrieve)
  • Re: Setting a Default Value
    ... the make the Sql call from within Application_Start in Global.asax. ... the query string value each time I needed to ... going to assign the default value through a database query. ... run the following SQL query: select * from table where x = 1 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: MultiSelect Property
    ... > For Each vItm In Me!lbSelectContract.ItemsSelected ... > Dim stCriteria As String ... >> SQL query). ... >> the multiselect list box appears in the SQL query. ...
    (microsoft.public.access.devtoolkits)
  • Re: Crosstab counting
    ... Michel, thanks for the response. ... I've built the table as shown, ... After reading the syntex sql multiple times, I can see the statement forming ... The SQL query is the same as yours: ...
    (microsoft.public.access.queries)
  • Re: Report in VBA - FilterName or WhereCondition
    ... strWhere clause as you advise. ... If you use un-bound controls in your sql query, ... strWhere in the filter parameter of the report, the defense is that you can ...
    (microsoft.public.access.modulesdaovba)