Limiting Record Occurence

[IbeKaba]

Keep in mind, I just started a new job and using Crystal literally two weeks ago. So if my question is really elementary, that's why.

With that in mind, here is my question:
How do you limit your result to only one occurence of the primary key (Account Number)?
I'm working with two tables where the primary key in one is not the primary key in the other. Consequently, it's giving me multiple records with the same "account number". I just want to get one occurence of a record whenever the arguement is true.

I hope that makes sense. Otherwise here is a little bit of a visual:

table1
Acct Number -- Name
1212 --- Billy
1313 --- John
1414 --- Mary

Table2
Account Number --- Flag Code
1212 --- 59
1212 --- 89
1313 --- 99
1313 --- 62
1414 --- 89
1414 --- 99

Let's say I ask it to give me from Table2 all Account Numbers with "59" or "89" flag code
It gives me
1212
1212
1414

But I only want it to give me
1212
1414



Thanks in advance
ibe

 

[Durango122]

hi
seam you have a group somewhere
remove the group and simply create a formula
in the record section
select
a.account_number, b. account_number, b.code_flag
from
table1 a, table2 b
union on b.account_number = a.account_number
where
b.code_flag in [59,89]

Durango122
Remember to used all fingers when waving to policemen

 

[IbeKaba]

Thanks for the response. But I don't think I understand.
I don't have any grouping on my report that I'm aware of. I'm thinking there has to be a button in crystal you can turn off and on to get this effect. Or maybe it has to do with the way the two tables are joined, but I can't for the life of me think of any other joins that would do any better than the inner join it already has.
Again thanks, but if you think of anything else, please let me know.
ibe

 

[PolkManiac]

Are you sure table 2 has no duplicates? Have you tried the Select Distinct feature?

Since you're pulling from two tables, but the Account number isn't the same thing on each table, how are you linking the two tables, on what field?

 

[Madawc]

Looking at your data, I feel you should start from 'Table 2'. This evidently has the 'account number' of Table 1, even if it calls it something different. The database expert will let you link any two items of the same sort, though it defaults to linking fields of the same name and type. Look at the Database Expert, which allows you to clear links and make new ones.

As Durango122 said, you need to group on the Table 1 account number. Grouping is something you yourself control - you'll find it under Insert.

Once you have the group, you can right-click on detail and suppress it. Show the desired code in the group header or footer.

All of this should be independent of your Crystal version, 8.5 or 9 or 10 or 11, whatever. But you should give it in future, because sometimes the answers would vary a lot.

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

 

[IbeKaba]

Thanks everybody. I did it. As you all suggested (one way or the other) I created a grouping, and inserted all my info in that section, then I suppressed the detail section. Though the record count stayed the same, it only showed one distinct reference to a record, and when I exported it to Excel, only these were sent.
Thanks a lot guys/gals
ibe