Help Counting Matching Values Across Multiple Fields

[omcompany]

I need to show the count of Datecode_1 and Datecode_2 as well as list the different Datecode(s).

I am using Crystal 11 connected to an Access db.

Here is an example of the data;

Line Datecode_1 Datecode_2
1 abc xyz
2 xyz abc
3 abc abc
4 xyz xyz
5 xyz !@#

The results that I need to show are;
Datecode(s) Used
abc 4
xyz 5
!@# 1

I have tried to grouping on the Datecode_1 and _2 fields and use something like;

//@DatecodeSum
(Count ({Summary.Det1DateCode})) + (Count ({Summary.Det2DateCode}))

and get close but have not been able to get the results that I need. I have tried sevearal other things that have gotten me nowhere as well.

Thanks in advance for any help. Your time is appreciated.

 

[lbass]

How many unique datecodes are possible? Does the list of datecodes change?

-LB

 

[omcompany]

How many unique datecodes are possible?

It is possible for there to be a countless number of datecodes. It is realistic to have two to five. I would need the option to be there though to cover the instance that there would be lets say 20. If a cap or limit was needed I could safely say that 10 would cover 99% of situations.

Does the list of datecodes change?

From report to report they will. Once all of one datecode is used up the next one will be used. The amounts of each vary but usually one report (the report is a daily summary report)will only have two to three different datecodes. This is true for both Datecode_1 and _2.

 

[lbass]

You might want to try merging the two fields into one by using a command ("add command" above your table list in the database expert) like this:

select table.datecode1 as datecode, table.line
from table
union all
select table.datecode2, table.line
from table

Then you could insert a crosstab in the report header or footer that uses {command.datecode} as the row, and count of table.line as the summary field.

-LB

 

[omcompany]

Thank you for the response.

I am getting an error when trying to create the Command.

Database Connector Error: 'DAO Error Code:0xbf5 SourceAO.Database Description: Too few parameters. Expected 2.'

I am assuming it is in my formula but can not say for sure. Here is my formula.

Select Summary.Det1DateCode as Datecode, Summary.Line/ShotPoint
from Summary
UNION ALL
Select Summary.Det2DateCode, Summary.Line/ShotPoint
from Summary


After putting in the formula I set the Parameter name (DetDatecode), leave the Value Type as String, put a check in Allows multiple values (not sure if this is needed) and select OK. That is when I get the error above.

 

[MCuthill]

omcompany,

My SQL is a bit on the rough side, but I think that the "/" in the field name is perhaps the cause. (totally a guess though...)

Perhaps wrapping this field in [ ] will resolve?

Select Summary.Det1DateCode as Datecode, [Summary.Line/ShotPoint]
from Summary
UNION ALL
Select Summary.Det2DateCode, [Summary.Line/ShotPoint]
from Summary

Again, perhaps not at all the case, but might be worth a shot.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

What parameters? That error does not refer to the use of report parameters that you create in the field explorer.

From your existing report (before trying the command), please copy the "Show SQL Query" and paste it into this thread.

-LB

 

[omcompany]

Mike-

Thanks for the help. That sure did the trick. Now I will try to get it to work in the cross tab. Thank you all very much.

 

[omcompany]

LB-


Here is the SQL Query that you mentioned. Once I used the [] for the / in the field name seemed to be the issue. I am sure it would not have been one if I had originally posted the correct name of the field instead of a generic one. My apologies.


SELECT `Summary`.`Project`, `Summary`.`JobNumber`, `Fixed_Project_Details`.`Client`, `Fixed_Project_Details`.`Location`, `Summary`.`TrackerDate`, `Summary`.`DrillerName`, `Summary`.`Helper_1`, `Summary`.`Helper_2`, `Summary`.`RigNumber`, `Summary`.`Buggy_1`, `Summary`.`Buggy_2`, `Summary`.`Line/ShotPoint`, `Summary`.`Re-Drill`, `Summary`.`Backup`, `Summary`.`Det1Resistance`, `Summary`.`Det2Resistance`, `Summary`.`HoleDepth`, `Summary`.`TopofCharge`, `Summary`.`Lithology_1`, `Summary`.`Lithology_2`, `Summary`.`ShooterCaution`, `Summary`.`TrackerTime`, `Fixed_Project_Details`.`DetonatorLength`, `Summary`.`ChargeWeight_1`, `Summary`.`ChargeWeight_2`, `Summary`.`ChargeWeight_3`
FROM `Fixed Project Details` `Fixed_Project_Details` INNER JOIN `Summary` `Summary` ON (`Fixed_Project_Details`.`Project`=`Summary`.`Project`) AND (`Fixed_Project_Details`.`JobNumber`=`Summary`.`JobNumber`)

 

[lbass]

You have everything working now with the union all, right?

-LB

 

[omcompany]

Once I insert the Command in the report the rest of the report (not entirely) brakes. I am now getting the same Line/Shotpoint details for every instance of a Datecode.

Here is the SQL Query.

Select Summary.Det1DateCode as Datecode, [Summary.Line/ShotPoint]
from Summary
UNION ALL
Select Summary.Det2DateCode, [Summary.Line/ShotPoint]
from Summary

I got this warning when I added the Command.

“More than one datasource or a stored procedure has been used in the report. Please make sure the no SQL Expression is added and no server-side group-by is performed.”

 

[lbass]

You should be setting up the command as the sole datasource for the report. Which fields in your actual query are Det1DateCode and Det2DateCode--I don't see them there, i.e., what are there actual field names?

-LB

 

[omcompany]

You should be setting up the command as the sole datasource for the report."

Would this mean since I have several fields that the report references (the datecode summary is only one of them) that a subreport for the datecodes with the sole datasourse set there?

"Which fields in your actual query are Det1DateCode and Det2DateCode..."

The field names are {Summary.Det1DateCode} and {Summary.Det2DateCode}.

 

[lbass]

No, I was suggesting that you need to convert the report to use the command instead of all of your linked tables. I guess you could insert an unlinked subreport that does this, as long as your query provided the correct dataset. What you should NOT do, is add a command to a report that is already using tables (except if it is used just to populate a parameter picklist), as any linking would occur locally, and be very slow.

To convert to a command only as your datasource, you would do the following:

If you want to use your existing report design, you can create the union all query by copying your query into the add command area (adding your Det1DateCode field as the first field), adding "Union all" and then pasting it again, changing Det1DateCode to Det2DateCode in the second half of the query, like this:

SELECT `Summary`.`Det1DateCode` as DateCode,`Summary`.`Project`, `Summary`.`JobNumber`, `Fixed_Project_Details`.`Client`, `Fixed_Project_Details`.`Location`, `Summary`.`TrackerDate`, `Summary`.`DrillerName`, `Summary`.`Helper_1`, `Summary`.`Helper_2`, `Summary`.`RigNumber`, `Summary`.`Buggy_1`, `Summary`.`Buggy_2`, `Summary`.`Line/ShotPoint`, `Summary`.`Re-Drill`, `Summary`.`Backup`, `Summary`.`Det1Resistance`, `Summary`.`Det2Resistance`, `Summary`.`HoleDepth`, `Summary`.`TopofCharge`, `Summary`.`Lithology_1`, `Summary`.`Lithology_2`, `Summary`.`ShooterCaution`, `Summary`.`TrackerTime`, `Fixed_Project_Details`.`DetonatorLength`, `Summary`.`ChargeWeight_1`, `Summary`.`ChargeWeight_2`, `Summary`.`ChargeWeight_3`
FROM `Fixed Project Details` `Fixed_Project_Details` INNER JOIN `Summary` `Summary` ON (`Fixed_Project_Details`.`Project`=`Summary`.`Project`) AND (`Fixed_Project_Details`.`JobNumber`=`Summary`.`JobNumber`)
Union All
SELECT `Summary`.`Det2DateCode` as DateCode,`Summary`.`Project`, `Summary`.`JobNumber`, `Fixed_Project_Details`.`Client`, `Fixed_Project_Details`.`Location`, `Summary`.`TrackerDate`, `Summary`.`DrillerName`, `Summary`.`Helper_1`, `Summary`.`Helper_2`, `Summary`.`RigNumber`, `Summary`.`Buggy_1`, `Summary`.`Buggy_2`, `Summary`.`Line/ShotPoint`, `Summary`.`Re-Drill`, `Summary`.`Backup`, `Summary`.`Det1Resistance`, `Summary`.`Det2Resistance`, `Summary`.`HoleDepth`, `Summary`.`TopofCharge`, `Summary`.`Lithology_1`, `Summary`.`Lithology_2`, `Summary`.`ShooterCaution`, `Summary`.`TrackerTime`, `Fixed_Project_Details`.`DetonatorLength`, `Summary`.`ChargeWeight_1`, `Summary`.`ChargeWeight_2`, `Summary`.`ChargeWeight_3`
FROM `Fixed Project Details` `Fixed_Project_Details` INNER JOIN `Summary` `Summary` ON (`Fixed_Project_Details`.`Project`=`Summary`.`Project`) AND (`Fixed_Project_Details`.`JobNumber`=`Summary`.`JobNumber`)

Then do not link the command to anything (ignore the message). Then replace all fields in the report with the corresponding command fields, including in formulas, sort areas, selection formulas, and groups. Once you no longer see any references to tables in the field explorer, you can remove them in the database expert. This assumes that your current query selects the records you want, i.e., that all criteria in your selection formula pass to the SQL. If you have selection criteria that are not appearing in the Query, you need to build them in, since you will no longer be using the select expert in the main report.

-LB

 

[omcompany]

The final solution was simple on my part. The programmer for the app created a summary table that I can pull from. It seemed to be easier to do with code rather than within Crystal. Thank you for your help and time on this one. I learned from it if nothing else.