Open table command for Crystal Xi

[Hugo1]

Is there an open table command I could use in Crystal Xi.
I have a name field in Crystal Xi and when this is passed to the report i want crystal to open the table with the corresponding name. Does anyone know how to do this?

 

[synapsevampire]

No, Crystal won't do that, you point at tables or write SQL against connections.

Perhaps it can be done in a fashion, but you need to post technical information, such as your software version and the database being used.

-k

 

[Hugo1]

Thanks for that. For what it's worth I am using Access 2000 database, crystal Xi and an Access ODBC driver.

 

[synapsevampire]

You might be able to pull this off within one or more queries in the MS Acess database, with the final query being the record source for the report.

I'm not familiar enough with Access to show the syntax, and stating that you want to open a table is meaningless, share what you would be returning from these tables.

You might also consider posting in a MS Access forum.

-k

 

[Hugo1]

Thanks much appreciated, the table i'm trying to open is a sensor table which holds a temperature field and a date field. I have 500 sensor tables in the access database which record temperature and value for different sensors throughout the site. The crystal report I'm designing is supposed to have a pick list of all these sensors and when the user selects a sensor, crystal would open that table in access and the crystal report would display the temperature and dates for that sensor selected by the user. Hope that makes sense!

I'll post this on an access forum also.

 

[Madawc]

Crystal is a report-writing tool, not a full language. You tell it which tables you want and it opens them for you, without giving you any control over the process.

If data may not be there, you can test ISNULL for a field in a record that actually isn't there. This is normally done at the level of records, a left-outer link from A to B when some instances of A don't have a corresponding B.

As synapsevampire said, you can use SQL. A stored procedure would collect data from a number of tables that might or might not be there. Assemble them into one grand stored-procedure table that Crystal could then read and also link to other tables.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Hugo1]

Thanks Madawc and Synapsevampire,

The SQL query i've got consists of:
SELECT Mid(Actions.Command,InStr(actions.command,"],")+2,100) AS SENSOR, mid(actions.command,8,8) AS SENSORTABLE
FROM Actions
WHERE (((InStr(actions.command,","))>0));

This brings back the Sensor name.
I'm trying to work out the syntax for linking the sensor name to the corresponding Sensor name in the sensor table, and open that table to report the temperature and date fields.

I've posted this on an Access forum also.

 

[Madawc]

Is there a separate table for each sensor?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Hugo1]

Yes, approx. 500 separate tables for each sensor

 

[Madawc]

Sorry, I have no idea how to do this in SQL. Maybe ask at the SQL forum?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

Or you could use SQL to merge the data from the 500 tables into one temporary table, and then link in Crystal.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Hugo1]

thanks again for your help. Much appreciated!

 

[synapsevampire]

You're on the right track here, and my approach would be to create a single MS Access query which returns all of the data in a single record source for Crystal's use.

I suspect that some Access guru could put this together, I'm not one of them.

Again, use the MS Access forums, and the suggested output should be something akin to what a UNION ALL query would produce, including the table name as a column called datasource or some such.

In Oracle I could work this out in a Stored Procedure, not sure how to do so in Access.

-k

-k

 

[Hugo1]

thanks synapsevampire, I'll try that and see how i get on!

 

[Hugo1]

i've used the query:
Select * From tblA
UNION ALL
Select * From tblB
which has worked and merged the date and value fields for all sensor tables into one table.

I'm not sure how to link this table to each sensor table.

Hope that makes sense!

 

[synapsevampire]

There are probably metadata tabls in Access that list the tables, this is how I'd do so in other databases, hit the Access forums.

-k

 

[Hugo1]

thanks