Using Excel as a db... 1

[kcsilentbob]

Hello all!

I am running Crystal XI.

Trying to bring in a list of ID's from a main DB, and compare it to a list in Excel. The goal of this is to eliminate ID's from the main DB that is listed in the list in Excel.

I have tried a few things such as changing the record selection to be:

DB.AgtID <> Excel.AgtID

but no records show. I tried the sub report and think that I may be doing something wrong when trying to link them. I think I just may need better intructions.

Can anyone help?

 

[synapsevampire]

I don't think that Crystal will allow for a Left Outer join with disparate datasources, so within Crystal you'd need a subreport, which is sloooooooooooow.

I'd suggest linking (not importing) both of the data sources into an Access database (use ODBC, listed under file types), and then use the MS Access query wizard for finding unmatched, and then using the Access query as the source for your Crystal Report.

-k

 

[lbass]

If you go the subreport route, you could add the subreport (unlinked) in the report header. In the subreport, create a formula like:

whileprintingrecords;
shared stringvar IDs := IDs + totext({Excel.AgtID},0,"") + ", ";

Place the above formula in the detail section of the subreport.

Then in the main report go to the section expert->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar IDs;

totext({DB.AgtID},0,"") in IDs

-LB

 

[kcsilentbob]

I have the db set up and am now having a hard time connecting the querey to Crystal.

My make new connection screen shows:

Access Objects
ACE's
Objects
Queries
Relationships

I don't have a views to list what is in the database. Also when I try to move over queries there is a no read error?

Can you help with that?

 

[kcsilentbob]

Wanted to add I am trying to connect to Access 2000, and using the DAO create new connection

 

[synapsevampire]

Have no idea what "I don't have a views to list what is in the database. " means.

Have you successfully connected to the 2 data sources and created the query in Access? Saying you have a DB setup means nothing more than that you've created an Access database.

Please state what you have successfully done, and what you need help with.

You can set up a File or an ODBC connection to the Access database, I would go with ODBC.

And one can't move queries over to Crystal, you can select a MS Access query as a datasource in Crystal, it will be listed under Views (using ODBC).

-k

 

[kcsilentbob]

OK I was being a little thick headed.

I have:

Set up the query using the wizard as you had suggested, and used without matching.

I have now gained connection to the .mdb file via crystal.

So my question now is:

I have the main DB, and the Excel db listed in tables. What do I need to bring over that will bring the query over?

Reading what you say do I need to create a table to bring that data over? I guess this is where I am a little confused now.

 

[synapsevampire]

You don't do anything in Access, go poinjt to the Access database from Crystal using ODBC, make sure that Views are selected as data sources within Crystal, and your query will show up as a datasource in Crystal.

You don't push anything to Crystal, Crystal connects to datasources, so once you point Crystal to the Access DB you can select your new query and you're almost done because Access did the heavy lifting for you.

-k

 

[kcsilentbob]

That did it. Thanks for your help.