inner join not working 1

[kwalters135]

I have a master table that has a one to many relationship to a detail table. This works fine. But when I add a third table (categories) that needs to have an inner join to the detail table, I get erroneous results. In the linking window, the join to the third table (categories) has a one to many relationship from the detail table to the categories table. I can't figure out how to change this to a one to one relationship.

I'm using crystal XI sp I, and the database type is dbase III (.dbf).

 

[lbass]

What do you mean by "erroneous results"? Is your join between the master and detail table an inner join? It would probably help if you supplied the fields available for linking.

-LB

 

[kwalters135]

Linking details:

Master.File --> Detail.File
File is string length of 8 in both Master and Detail tables.

Detail.Category --> Categories.Category
Category is string, length of 1 in both Detail and Categories tables.

The result set returns only one record, which happens to be the first record in the detail table for the file date (file field) that I select.

The join type between the master and the detail is an inner join, though it does return many detail records per master record (which is what I want) when I remove the Categories table from the process.

 

[lbass]

Try a left join from {Detail.Category} to {Categories.Category}. It sounds like there might be nulls in the latter field.

-LB

 

[kwalters135]

Getting closer ...

I made it a left join as you described. I got the right number of records from the master and detail, however it's not pulling any data from the categories table.

There are only 13 records in the categories table, and all the fields have data. I also tried all of the types of enforcement rules for the left join, but they all gave the same results.

 

[lbass]

I wonder whether the category fields you are linking are of different lengths or formats--are you sure they match? Can you show examples of each of the category fields?

-LB

 

[kwalters135]

The category field is a 'character' type, length of 1 in both the Detail and Categories tables. Examples from Categories table:
Category field Description field
-------------- -----------------
A Cash & Due
E Investments
1 Non-Maturing Deposits

The detail table has 425 fields, here are 2 fields from a record.

Detail table:
Category field File field
-------------- ----------
A 199802-M
1 199802-M

 

[lbass]

Hmm, this is odd. Have you verified the database? (Database->Verify database) I think this should work as you have it set up now.

What happens if you add only the Categories table to a report--does it populate?

If you remove the Categories table, and only use the Master and Detail, is the category field populated in the details table?

-LB

 

[kwalters135]

I verified the database and it checked out ok. Also ran a report with just the Categories table and it looked fine.

Before I added the Categories table, I was generating the description from the Category field in the detail table using formulas, so I know the category field is ok in the detail table.

My boss picked up the Mastering and the Complete Reference line of books for CR XI last night, but we haven't found anything in them yet for this problem. I think we're to the point of submitting a trouble ticket to crystal decisions. Thanks for your help on this. I will keep an eye on this forum and try to return the favor if possible.

 

[lbass]

The Categories table comes from the same database as the other tables, right?

-LB

 

[lbass]

Also, what is your record selection formula?

-LB

 

[kwalters135]

The database type is dbase III, so I'm using the xbase driver in crystal. I just started here about a month ago, so I'm new to dbase. They use dbf viewer pro by legendsoft.com to access the data. In this tool, there is no way to have more than one table per database. So each table is in its own db. (sounds strange, i know) Since it's not a typical SQL db, I can't view the SQL statement.(that option is grayed out in the database menu in cr)

I have one filter criteria set up in the select expert which is set to a parameter that is requested upon the report launch.

{gd.FILE} = cstr({?rept_dt})

 

[lbass]

Can't tell what table the {gd.file} is from...

Anyway, try removing the Category table from the main report and instead, add the Category table to a subreport and then link the subreport to the details table on the category field. Place the subreport in the details section. Does it then display the correct result?

-LB

 

[kwalters135]

I restored the original db's and the report now works as designed. Apparently, at some point during adding/changing indexes on the db, it reached a point where CR could not read it properly. So using explorer, I just removed the db's and restored the original db's. This allowed the report to correctly pull data from the categories table.