Can I link use two linked fields to link two tables? 1

[goolawah]

I'm using Crystal v10 with DBase IV tables.

Table 1 is transaction detail and includes fields-
Number, Item, Qty, Location.

Table 2 is stock status and includes fields-
Item, Location, OnHand.

I am using a left inner join to link Item and Location in Table 1 to Item and Location in Table 2.

I have checked that each record in Table 1 has just one record in table 2 with the same combination of Item and Location.

The result I am looking for would be a list of all records in Table 1, showing information from the records in Table 2 that match on Item and Location. (in particular I need Qty from Table 1 and OnHand from Table 2)

What I get is only those records in Table 1 that match the first record in Table 2.

I hope that's all clear enough for someone with more experience to tell me what I'm missing. I've spent at least three days on this and still stuck.

All suggestions appreciated.

Cheers,

 

[goolawah]

I thought I should also mention that when I use a Left Outer join I get all the records in Table 1, but the information required from Table 2 is only shown for the two records that match the first record in Table 2. (Does that make sense?)

 

[IanWaterman]

Its probably your select statement, putting restrictions on both tables. Please paste on here so we can see what might be limiting datast returned.

Ian

 

[goolawah]

Thanks for that.

The complete selection formula is as follows -

trim({OeDet.ITEM})= "LA196____C"
and {OeDet.TYPE}= "I"

Oedet is Table1.
It is linked to Table2 by fields Item, and Location.

The selections above are to limit the output to just one item code (LA196____C) and to just invoices (Type= I)

Would it help to upload some screen shots?
If so, how?

Thanks,

 

[lbass]

You should be using left outer joins FROM oeDET TO the other table. Also, be sure to place your fields in the detail section of the report.

-LB

 

[IanWaterman]

Are you sure there are more than two matching records in table 2 for the conditions within your select statement.

Can you show us the full SQL statement created by Crystal

Ian

 

[goolawah]

Thanks for feedback so far. It prompted my to try a couple of ideas, unfortunately without success.

In response to the questions above -

I have created a simplified 'dummy' report to make it easier to work through this.

The full SQL select statement is as previously outlined and repeated here.

{OeDet.TYPE}= "I" and
trim({OeDet.ITEM})= "LA196____C"

-only this time I copied it from
Report | Select Expert | Show Formula
is that what you meant?

As you can see, the selection applies only to oedet, which is Table 1.

This is linked to Table 2 by two links, one linking Item to Item and the other linking Location to Location, both Item and Location exist, and are populated, in both tables.

Table 2 has just one record for each unique combination of Item and Location in Table 1. (In my test database, Table 1 has 112 records that match the criterion, {xxx.ITEM}= "LA196____C" and Table 2 has 41 records that match.

I have tried to provide the relevant information without writing War & Peace. Is that enough?

 

[IanWaterman]

The SQL satement can be found at
Main Menu

Database -> Show SQL query.

Ian

 

[goolawah]

That seems to be a problem. That option is greyed out, presumably because I am accessing the DBase files directly. I did try setting up an ODBC data source to see if I could discover anything by that route, but the problem persisted and the report was extremely slow to run. Probably unacceptable to the user, so I canned that idea. However the option

Main Menu
Database -> Show SQL query.

Is available in that version of the report.

I have also tried 'dummying up' a new test database and I am beginning to wonder if there is a problem with the fields I am linking., like some spaces or something else 'invisible'.

Rather than drain other people's energy, I'll pursue that a bit further.

However, It would help to have confirmation (which I think I already have) that there is not a problem 'in principle' with having two fields, and two links, linking the tables?

 

[goolawah]

I have learned something. I have added more records to my test database to provide a more representative sample.

I can get the result I am looking for by deleting the link on Location so that there is only one link (Item).

I then added another selection criteria
Table_1.Location = Table_2.Location

This produces the right result but in the live data, where there is a very large number of records in each table, it takes forever to come up with anything. (Understandably as I assume it has to evaluate every combination).

I'd appreciate any suggestions on how to overcome this?

 

[Erbium]

When test data works OK but live tables take a real long time or 'forever', often the problem is no index the join can use. The result is that the whole joined table is scanned for each record. Test files can do this for a few records but large files may hang forever.

Use a tool that shows you the indexes on the tables.
The joined field should show up as an index on the table being joined to.

Tools like WinSQL show indexes. Toad may too.