Retrieving all data on related but not joined data 2

[copguy]

Version: Crystal XI
Datastore: Access 2003

Scenario: I have two separate tables; one that contains data about employee authorizations (AUTHORIZATIONS) and the other that contains data about employees enrolled (ENROLLED) in courses. These tables don’t share a unique identifier.

From the AUTHORIZATIONS table I need to capture the “department”, “job specialty”, and a count of “position number” (primary key). This will produce the number of positions for a “job specialty” within each “department”.

From the ENROLLED table I need the “department”, “job specialty”, and count of “enrolled number” (primary key). This will produce the number of enrolled for a “job specialty” within each “department”.

I’ve been able to do this without any problem. The problem comes when I want to show the data side-by-side. For example:

Department
Job Specialty (Count of Pos#) (Count of Enrollment#)

I’ve been able to be successful in displaying the values in which the “job specialty” exists in the “department” in both tables, but when the “job specialty” only exists in one of the tables, I don’t get a result.

For example: The AUTHORIZATIONS table contains records for “job specialty” (xyz), but there aren’t any records in the ENROLLED table for “job specialty” (xyz). And vice versa.

How do I get the report to show all instances of “job specialty” from both tables and then provide the value? In some cases the returned value will be zero if one of the tables didn’t contain any records for the ‘job specialty”.

 

[synapsevampire]

Since the data exists in Access, why not create queries in Access to provide the aggregates and use a query as the CR data source?

I suspect your difficulty is that you're joining the tables by DEPT or some such,not sure, you don't speak to the crux of this which is how you are providing anything or the joins used.

One such method might be to create a UNION ALL in either a Access query to return all data, or you can use a Add Command listed under the Access data connection to paste in the SQL to do this.

Something like:

select 'auth' tbl, department, specialty, number from auth
UNION ALL
select 'enr' tbl, department, specialty, number from enr

Then you can group by the department and specialty and provide conditional counts using Running Totals and in the evaluate->use a formula place the criteria for each, as in:

tbl = "AUTH"

-k

 

[lbass]

Use add command to create a union statement:

Select 'Auth' as type, `Auth`.`dept`,`Auth`.`spec`,`Auth`.`posno`
From `Authorizations` `Auth`
Union all
Select 'Enr' as type, `Enr`.`dept`,`Enr`.`spec`,`Enr`.`enrno`
From `Enrolled` `Enr`

Then create formulas like:

if {command.type} = "Auth" then 1

if {command.type} = "Enr" then 1

Then insert groups on dept and spec and insert sums on the above formulas at the group level. Then suppress the details section.

-LB

 

[lbass]

Sorry, I tried submitting this 3 times before success, and didn't notice your post, SV.

-LB

 

[synapsevampire]

Nae worries, LB, happens to me all to often, part of this Lovely Cold Fusion environment...

-k