Oracle Journal Tables - Oracle Clinical

[GMAN33]

Hi All

CR XI Pro with Oracle 9 DB using Oracle Clinical...if anyone knows what that is ;-)

I have an Oracle_Accounts table that lists all users that currently have accounts. I link that to the clinical studies table that will show me what studies they have access to.

Now there is also a Oracle_Accounts_JN table that has some employees that either no longer have an account or no longer have access to a study.

For auditing purposes, I need to have both of these tables pull data for studies for both deleted users and current users...these tables would be the first ones that I use to start the report.

I am sure there is a way to write a formula that compares these two tables user accounts.

I used the Oracle_Accounts table and that worked but did not give me the users that were removed and then I used the journal table and that didn't give me all of the current users.

Any suggestions? Thanks for the help

 

[synapsevampire]

Instead of using the tables directly, try selecting Add Command under the datasource and use real SQL, as in:

select A.user, A.User_id, B.studies from
(
select user from Oracle_Accounts
UNION
select user from Oracle_Accounts_In
) A, clinical_studies B
where
A.User_id = B.User_ID

Or ask your dba to supply the proper SQL or a View as I assumed that here's a user id field involved here to ensure uniqueness.

An alternative might be to use a subreport to return the Oracle_Accounts_In tables values.

Not sure why you think a formula could be written to compare these things, that's not how SQL or Crystal works, formulas don't compare databases.

-k