Edit selection Formula question

[mayor]

I have 2 ODBC databases that are linked via a common field (call_number). The first database(callheader) contains the header information for help desk calls. The second database (call_activities) contains the various activities that are associated with the header record. Each call can have several activities with different dates. I can easily create a query (in Crystal Reports 7)that will tell me the activities within a date range. For example, a query from(call_activities) showing all activities for all calls between july 1 - july 7. <br>If a call originated before july 1, this query will not show the activities prior to july 1. To get all of the activities I change the select formula to pull from the header database (callheader) the particular calls that I want to see. What I am doing is a 2 step process to get my final report. In a nutshell, what I want is the following:<br><br>If a call has activities in the date range specified, then give me all the activities for the call.&nbsp;&nbsp;All in 1 pass.&nbsp;&nbsp;<br><br>How should a selection formula look in order to achieve the above statement.<br>Thanks in advance,

 

[HoustonGuy]

I'm kinda confused - so let me give an example to make sure I know what you're doing.<br><br>You can have a data return that looks similar to this:<br><br>Activity1&nbsp;&nbsp;07/01/2000<br>Activity2&nbsp;&nbsp;07/01/2000<br>Activity3&nbsp;&nbsp;07/03/2000<br>Activity4&nbsp;&nbsp;07/04/2000<br>Activity5&nbsp;&nbsp;07/05/2000<br>Activity6&nbsp;&nbsp;07/05/2000<br><br>You want to make sure you get all of the activities even if they have the same dates - is this correct?<br><br>For this example you must use the formula for the date range against a date field or else it will not work.<br><br>{Call_ActivitiesDateField} in Date(2000, 07, 01) to Date(2000, 07, 07)<br><br>This will restrict all of the records to be between these dates if this formula is placed in the record selection for the main report.<br><br>If the data you want to see id placed in the detail section of the report then it should show all records even if they have the same dates.<br><br>I may be completely off and misunderstand you completely. If so please clarify more and I'll get you there.<br><br>Hope this helps. <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[mayor]

<br>Hi Cody <br>As promised, here is an example of the problem that I am having regarding formula questions (7/13/2000 posting)<br><br>2 example databases involved. For simplicity sake I will use 1 call and minimal fields.<br><br>call_header database<br>call_number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;client_number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;opening_call_date<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;109&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000629<br><br>call_activities database<br>call_number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;activity_date&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000629&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000630&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000701<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000702<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000705<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;001447&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20000708<br><br>Databases are linked via the common call_number field. <br>In plain english, this is what I am trying to accomplish. <br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If a call has activities for the date range specified, give me all the activities for the call.<br><br>&nbsp;Using the above database and criteria, if my query asked for the activity_date&nbsp;&nbsp;range of&nbsp;&nbsp;20000701 thru 20000708 the output woud include all the activities for all calls(within the range).&nbsp;&nbsp;So in the above example, I would get the complete thread even though I only asked for a specific date range of activities.&nbsp;&nbsp;This would give me a complete picture of all the call activities.&nbsp;&nbsp;In real life the databases would include maybe 50 calls.&nbsp;&nbsp;Out of those, 40 (or so) would have been open and closed within the activity date range specified. The other 10 could have activities going back to dates prior to the range specified.&nbsp;&nbsp;<br><br><br>What I am actually doing now is running a query that asks for the activity_dates <br><br>({Call_activities.Activity_Date} = {?date_range}) <br>//or ({Call_header.Call_Number} in [&quot;000000&quot; ])&nbsp;&nbsp;<br><br>and if the the call originated before the specified date&nbsp;&nbsp;&nbsp;opening_call_date, <br>then&nbsp;&nbsp;I rerun the query asking for the date range or the&nbsp;&nbsp;&nbsp;call_header.call_number .<br><br>({Call_activities.Activity_Date} = {?date_range}) <br>or ({Call_header.Call_Number} in [&quot;001447&quot; ])<br><br>Sooo, what kind of formula would test each call and determine&nbsp;&nbsp;<br>a: what calls had activity within the date range specified<br>b: if there are prior activities to the date range specified, include the prior activities and do this in one pass of the database<br><br>Cody, I hope this makes the question clear.&nbsp;&nbsp;Thank you very much for your interest and your input.<br><br>Steve Koch (mayor)<br>Clovis Point Inc.<br>

 

[MalcolmW]

This SQL would do it I think.&nbsp;&nbsp;Try plugging it in to the SQL Query tool, and see if you get the results you want:<br>SELECT call_activities.activity_date<br> , call_header.call_number<br> , call_header.client_number<br> , call_header.opening_call_date<br>FROM call_activities INNER JOIN call_header ON call_header.call_number = call_activities.call_number<br>WHERE EXISTS (SELECT * FROM call_activities WHERE call_activities.activity_date BETWEEN {?FromDate} AND {?ToDate})<br>What this does is pick all the info for all the calls, then limits them to the ones that have any activity in the specified date range.<br>I presume you are going to use parameters for the data range... <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>