Missing Records

[Stupot2k]

I am having a problem when I carry out a query on an SQL database. Where I am looking to add a description from another table associated with a code, IE Personnel Number, and I want the Persons Name from another table.

If the first record does not have the Personnel number on it, because the operator has not entered it, the entire record is missing from the table, rather than just the Persons name being blank. The Personnel number is not part of the search criteria but just collateral information required in the report.

The tables appear linked properly, they normally only have one link based on Personnel number, I have had a similar problem with different reports and different table elements.

Can anybody help?

 

[lbass]

Do you have three tables? Please list the relevant fields from each table and tell us how you currently have them linked, e.g., there is a left join from {TableA.ID} to {TableB.ID}.

-LB

 

[Stupot2k]

I have a number of tables 4 in all. I'm not sure exactly what you mean by left join, but the table I add which causes the problem is SVC00100. I am linking from
{SVC00200.TECHID} to {SVC00100.TECHID} and placing {SVC00100.TECHNAME} into the report.

The link goes from the right side of SVC00200 to the left side of SVC00100.

Stu

 

[lbass]

You didn't really answer my questions, so let me respond in a general way. If you have two tables, TableA and TableB, and they are linked by ID, and all IDs you want in your report appear in TableA, but only some of them are found in TableB, then you need a left join from TableA.ID to TableB.ID, so that you can get results like:

TableA.ID TableB.ID
1 1
2
3
4 4
5 5

If you had an equal join, you would only get:
TableA.ID TableB.ID
1 1
4 4
5 5

So what you need to do is change your links so that the table that has all the IDs is your left table and other tables with missing IDs are linked to it by left joins (from the left table to the tables on the right). In the visual linking expert, you would right click on the link between the tables->options-> and check "left join."

-LB

 

[Stupot2k]

Thanks very much, I must have dozed through that part of the course, I've never strayed into link options before and it has made a few things a little more clear.

I have changed my report and it now works as expected.

Thanks

Stu

 

[Mike Lewis]

Stu,

I must have dozed through that part of the course

Now if that had been one of my courses, I wouldn't have given you a chance to doze off <g>. Anyway, you'd have been enjoying it so much you wouldn't have wanted to.

(It's when the instructor dozes off that you've got to worry.)

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com