Cstr Problem

[Viscount]

I am using CR9.0 with SQL

I am working with 3 tables:

S Table
C Table
P Table

In the S table and C table there is an auto number created when a new entry is made called Sequence.

In all the tables there is an ID Number - in an ideal world this is used to link all the tables.

S Table:

ID Number / Sequence

1 12
2 13
3 14
4 15
5 15

C Table:

ID Number / Sequence

1 12
2 13
3 14
4 15
5 15

What really happens is when an entry is being put into the P table the letter S or C is added before Sequence.

P Table:

ID Number / Sequence

1 c12
2 c13
3 c14
4 c15
5 c15
1 s12
2 s13
3 s14
4 s15
5 s15

What I want to do is find out which ID Numbers either have an entry is the P Table or have no Entry in the P Table. I can discount all ID Numbers from the S Table

What I have done is create a formula that creates a string with a "C"in front of the sequence in the actual report.

What I think that I need to do is Add a SQL command when selecting the fields that I wish to work with in the Database Expert - and then link the P table.Sequence directly to this command.

Is this possible..?

I hope that I have made myself clear - and I really appricaite any help anyone can give me.

Thank you in advance

Vis.

 

[lbass]

I'm not sure why the sequence field is even relevant. Why can't you do a left join from the C table to the P table, and then display {C.ID} and {P.ID} to reveal which are populated in P and which are not? You could also add:

isnull({P.ID})

...as a record selection formula to reveal only those ID's in C which are not in P. I'm assuming that you mean the S table really isn't relevant.

-LB