Unique Parameter question

[ziggs]

I need to make a report that checks for events that occur at a particular address. Unfortunately, I have to check two separate tables in the same database to do so (one's a verified address and the other is unverified). So, I'm trying to plan the best way to write the report. The address has three fields in one table called digits, street, apt and three fields in another table called wrongdigits, wrongstreet, wrongapt. So, what I want to do is create three parameters called digits, street, and apt. From there I want to enter the three parameters and check both tables and report back the info from both tables on the same report.

So, here's where I'm lost. To set up a parameter I can only attach it to one field. I could set up 6 parameters instead of 3 but I don't want to do that. Also, when linking a subreport, I can only link a field to another field and I need to link 3 to 3.

Am I missing something? Can what I'm trying to do be done?

 

[flaubert]

It sounds like you need a UNION query for your report SELECT.

ie.

SELECT digits, street, apt [,1 ADDRTYPE]
FROM address
WHERE digits = digparam
AND street = strparam
AND apt = aptparam
UNION
SELECT wrongdigits, wrongstreet, wrongapt [,0 ADDRTYPE]
FROM wrongaddress
WHERE wrongdigits = digparam
AND wrongstreet = strparam
AND wrongapt = aptparam

This will look for the appropriate records in both tables and return the results in a single recordset. If you include the bits in square brackets, then you will have and extra field in the returned recordset telling you which table the address is from (0 for wrong, 1 for right). I've put it in brackets 'cos the exact syntax here depends on your database (it might be '1 AS ADDRTYPE). Actually, you might not even need to alias the name at all.

I'm not sure what you're trying to do with the subreport though...

 

[ziggs]

Thanks for the UNION tip. Obviously a more advanced feature for a beginner like me. I tried to look up the UNION in the helpfile and couldn't find out how to incorporate this in the Crystal Report. I would assume that you would have to use Crystal SQL Designer??? If so, I'm unfortunately lost on this topic, but willing to learn. My current report that hits on one table looks like this when I enter a parameter and then Show SQL Query:

SELECT
"INCIDENT_LOCATIONS"."INCD_EVENT_NUM", "INCIDENT_LOC_EVENTS_VW"."INCD_EVENT_NUM", "INCIDENT_LOC_EVENTS_VW"."CODE_NUM", "INCIDENT_LOC_EVENTS_VW"."INCD_WRONG_LOC_FETUR", "INCIDENT_LOC_EVENTS_VW"."INCD_CLSFN_NUM", "INCIDENT_LOC_EVENTS_VW"."INCD_DISPO_NUM", "INCIDENT_LOC_EVENTS_VW"."INCD_WRONG_STRT_NAME", "INCIDENT_LOC_EVENTS_VW"."INCD_WRONG_STRT_NUM", "INCIDENT_LOC_EVENTS_VW"."INCD_CALL_TIME", "LOCATIONS"."LOC_STRT_NUM", "LOCATIONS"."STRT_NAME", "LOCATIONS"."LOC_FETUR"
FROM
"PSIS"."INCIDENT_LOCATIONS" "INCIDENT_LOCATIONS",
"PSIS"."INCIDENT_LOC_EVENTS_VW" "INCIDENT_LOC_EVENTS_VW",
"PSIS"."LOCATIONS" "LOCATIONS"
WHERE
"INCIDENT_LOCATIONS"."INCD_EVENT_NUM" = "INCIDENT_LOC_EVENTS_VW"."INCD_EVENT_NUM" AND
"INCIDENT_LOCATIONS"."LOC_ID" = "LOCATIONS"."LOC_ID" AND
"LOCATIONS"."STRT_NAME" LIKE 'SELWYN DR' AND
("LOCATIONS"."LOC_STRT_NUM" LIKE '172' OR
"LOCATIONS"."LOC_STRT_NUM" LIKE '')
ORDER BY
"INCIDENT_LOC_EVENTS_VW"."INCD_EVENT_NUM" DESC


So, can I somehow use Crystal Reports rather than SQL designer to do a UNION? If so, how?