Problem testing for another record in table 2

[RobNauticus]

Hello all,

I would love some guidance on how to structure my reports within my new "normalized" Access DB. Most of my reporting will be based from a single table "DateLog". This table contains the following fields:

PK: JobID, LotID, TaskID, TripID
Other: TaskDate, ForemanID, TaskQty

Each record represents a particular trip to perform a particular task at a homesite.

The current part of the report I am working on requires me to take a record with a TaskID = 21 and subtract the TaskDate from the current date. This is where it gets more complicated...I only want it to show up if there isn't a record for TaskID = 9 and all of the other key fields match. (in other words only show the record if a previous task has been completed at that homesite.

Please let me know if I need to be more clear on any point.

Any guidance would be VERY appreciated!!!

Thanks,
Rob

 

[synapsevampire]

There are very basic pieces of information that should be included with posts:

Software version
Database (you did this)
Example data (show the fields and example values)
Expected outout

I'll assume that these basic field headers you've demonstrated are PK for primary keys, and another set which you've grouped under other, rather than the values being held within fields by those names.

Normally one would do this within Access in a query for speed and reusability using a query such as:

The first step is to group by whatever entity your testing the taskid's of within (you should post this), then just in case order the rows within by the taskid, then use the 3 formula method of:

group header formula:
whileprintingrecords;
booleanvar Got9:= false;

Details formula:
whileprintingrecords;
booleanvar Got9;
if {table.taskid} = 9 then
Got9 := True;

Then in the suppression formula for the group footer (right click it and select format section, X 2 next to suppress), use the following formula:

whileprintingrecords;
booleanvar Got9;
Got9
or
{table.taskid} <> 21

Suppress both the group header and the Details section, you'll be displaying at the group footer level.

Then use this in a formula in the group footer to display:
currentdate-taskdate

This will now suppress if task 9 was in the group, or if the taskid <> 21.

-k

 

[lbass]

Another approach would be to create a formula:

//{@hastask9}:
if {table.taskID} = 9 then 1

Then create a second formula {@task21diff}:

if {table.taskID} = 21 then currentdate - {table.taskdate}

Then format the detail section to suppress with the formula:

sum({@hastask9},{table.group}) > 0

Like SV, I'm unsure of what group level you are checking for the presence of task = 9.

This would suppress the row when the taskID = 21 and there was a previous task = 9. Note that your explanation seemed to be contradictory--did you leave the word "not" out?

I only want it to show up if there isn't a record for TaskID = 9 and all of the other key fields match. (in other words only show the record if a previous task has been completed at that homesite.

-LB

 

[lbass]

Let me amend that. Using my formula in the section expert suppression area would suppress all details if there was a task ID = 9. If instead you only want the difference to appear when there is no task ID = 9, but you want all other details to appear, then instead use the formula to suppress the field, not the detail section.

You could also suppress a detail row when task = 21 if there is a task = 9, by using the section expert->details suppression-> and entering:

{table.taskID} = 21 and
sum({@hastask9},{table.group}) > 0

Maybe that's what you intended. Not sure...

-LB

 

[RobNauticus]

I have not done any grouping as of yet. I was not sure if I needed it. It looks like I need to group the data at the JobID and LotID so "sum({@hastask9},{table.group}) > 0" would only look at the current house.

Just to be clearer, JobID and LotID define the house, TaskID represents a task complete at a house and the TripID increments if there are several trips taken to complete a certain task. The current example is to be able to tell how long a house has been ready to put up scaffold (TaskID =9) so that must obviously be null because if it exists the task is done. The other requirement is that the Framed Date must be present in that group (TaskID =21) The Framed record is where we get the date to calculate the days delinquent.


Should I not use the select expert for anything then, we will be suppressing them at the detail level?

CR Ver = Crystal 10.


An example of this data output would be something basic like this:

JobID, LotID, Date Difference
3, 54, 10 days
3, 55, 5 days
4, 99, 21 days

I hope this makes it easier to understand. Thank you both for the assistance. I will take a look at both of the solution you presented and check back in a while. Thanks Again!

Rob

 

[RobNauticus]

Also LB, I am not sure what you mean in the formula:

sum({@hastask9},{table.group}) > 0

could you please define what {table.group} exactly is referring to? I figure I change it to the group name that I am using. Just not sure how that evaluates to a number.

Thanks again,
Rob

 

[lbass]

Okay, it looks as though you only want those records to appear that meet these criteria:

1-There is a task ID = 21 in the lot group.
2-There is NOT a task ID = 9 in the lot group.

You should first insert a group on {table.jobID} and then on {table.lotID}. You can suppress the group headers and footers if you like.

You would need three formulas:

//{@hastask9}:
if {table.taskID} = 9 then 1

//{@hastask21}:
if {table.taskID} = 21 then 1

//{@task21diff}:
if {table.taskID} = 21 then currentdate - {table.taskdate}

Because of the display you would like, you could use GROUP selection, instead of suppression. You would go to report->selection formula->GROUP and enter:

sum({@hastask9},{table.lotID}) = 0 and
sum({@hastask21},{table.lotID}) > 0

Then place the job ID, lot ID, and {@task21diff} in the detail section. This should return the results you want.

If you wanted to, you could also right click on {@task21diff} and insert a maximum on it at the lot ID group level and then go to report->group sort and choose "All" and "maximum of {@task21diff}" descending as the summary so that the groups will sort in order of most delinquent to least.

-LB

 

[RobNauticus]

OK that is great, really close now. One more thing, why did you choose LotID in the selection formula? I dont think that is correct especially due to the fact that it is not even a numeric field.

I am still not sure what field would be appropriate there.

Please let me know what you think, thanks.

Rob

 

[lbass]

Whether the group field is numeric or not is irrelevant. In the formula, the groupfield (lot ID) is the second condition. The formula is summing {@hastask9} (and {@hastask21}) within each lot ID group. The group selection formula then selects those lot ID groups which meet the criteria for display.

Why do you say "close now"--is there something in the display that still doesn't meet your expectations?

-LB

 

[RobNauticus]

Nope I meant that I was close to understanding the logic behind what you are explaining.

It appears to be working! Thank you soo much again!

Rob

 

[synapsevampire]

Check my post, it I think it does the same thing as LB's eventual solution.

A Group selection is equivalent to suppression as the rows are still in the report.

Anyway, you probably learned a bit.

-k

 

[RobNauticus]

Sure did and now what you said makes perfect sense as well. Thanks!

 

[synapsevampire]

I see that I forgot to elorate on the proper solution which would be to do this is Access:

Create queries which properly limit the rows, the final query being the one exposed to Crystal. This will proive faster and allow for simplified maintenance and reusability.

An example of a query which would return all rows with taskid = 9:

select * from
(
select * from table
where table.jobid in
(select jobid from table
where taskid = 9)
) temp1
where jobid not in
(
select * from table
where taskid = 21
)

Can't recall Access syntax, but this is pretty vanilla. In Oracle one would probably use a Minus in lieu of a NOT IN for performance reasons.

Anyway, it returns all 9's that don't have a 21.

I assume that you're going with the Crystal method, but this is something much better suited for the database to handle.

-k

 

[RobNauticus]

Thanks for the input SV. I may move to that actually, the example used above was only a subreport of a larger report. I will need to do that for several separate subreports with different criteria.

So you suggest I create a query for each set of criteria and use them as my record sources in Crystal?

I believe that doing it that way would also enable me to create another report that has a spreadsheet-like view, showing each task date field across the page.

Let me know what you think. Your insight is most appreciated!

Rob

 

[synapsevampire]

Yeah, go with the Access based solution, and try to eliminate the subreports entirely.

Obviously you can then use that datasource by Excel as well rather than relying upon Crystal's export functionality.

-k