Restricting an ODBC select on Access data

[acidkewpie]

HI,

I have a large ODBC database that i'm accessing via a Crystal SQL Query (as i don't seem able to do inner selects, or decent JOIN's in Crystal reports itself) and I am wanting to exlude any results which have a corresponding entry in a simple access mdb file.

I'm not able to use both a predefined query and a datavase file in the same report, and can only use ODBC in SQL Query writer... and i'm at a loss for any way to accomplish this.

is there a possible approach? subreports? conditional suppression? any pointers would be really great.

thanks

Chris

 

[synapsevampire]

Create a Query in Access and base the report on the Query.

Even Crystal doesn't suggest using their SQL tool anynore.

If you require more assistance, please supply technical info:

Crystal: Version
Database: Access <version>
Example data
Expected Output

Text descriptions can be helpful, but a little time spent specing a design goes a long ways.

-k

 

[acidkewpie]

hi, well not sure how tables are going to look here, but...

oracle ODBC database, super simplified:

ID attempt bool
1 1 y
2 1 n
3 1 n
4 1 n
5 1 n
6 1 n
2 2 y
3 2 n
4 2 n
5 2 n
6 2 n
3 3 y
4 3 n
5 3 n
6 3 n

Access 97 table:

ID
4
6
9

now i'm after a report that contains just &quot;5&quot;, meaning it is the only one in the main Oracle DB that has never had a &quot;y&quot; against it, and is not in the Access table. If you want a context, the main table details attempts to retrieve data from a large selection of remote sites (obviously we do not try again after we succeed, i.e. a &quot;y&quot but we also want to exclude any shop that is out of action, which is held in a piddly little table. I can use the Query editor to get the data right from the Oracle db:

select ID from oracletable where ID not in (select distinct ID from oracle table where bool = &quot;y&quot.

but adding in the access as another inner select seems impossible. And the Oracle database has no chance of being altered or programmed in any way at all.

Access 97
Crystal Reports 8.5
Oracle.... not sure 8 i assume

Cheers

Chris

 

[synapsevampire]

You've a problem here as a Left Outer isn't available across different data sources in CR.

I'd link the Oracle table to the Access database, and then create a query to retrieve the proper data, and then base a CR report on the Access query, much impler and efficient.

-k

 

[acidkewpie]

I've been looking at Access and have been able to create what i want with a purely access table example. I don't like the idea of rely on access a i hate it, and think it's a piece of junk, but then i'm really ne to crystal, and it's looking like i might end up with a very similar opinion of that too!

 

[synapsevampire]

You don't need a pure Access solution, when you select New at the tables area you'll see an option to Link to a table, sleect ODBC and point at your table.

I know thatit's kinda cheesy, but hey, you're using Access for one table anyway, so you may as well leverage what Access does well, that being explore/link disparate data sources.

-k

 

[acidkewpie]

thanks for the tips, i'll see what access brings me tomorrow i guess. i know i can get both oracle and access up as odbc connections in CR but i need to join the oracle table with itself aswell as access... maybe i'm missing somethign abuot the ability to crete joins in CR, but if access works... best not rock the boat! cheers