Selecting distinct records 1

[NewB2007]

I have two tables to a mailing label report. One table I am pulling the Customer address info. The other table has a date that I am filtering by. I'm receiving multiple addresses. I know why... The "other" table has an ID attached to each record. What I need to do is be able to get the max ID for each client from the "other" table. I would like to be able to do the selection through crystal and not SQL if at all possible.

EX.

Customer Table
cust 1
add 1
city 1
state 1
zip 1

cust 2
add 2
city 2
state 2
zip 2

"Other Table"
id 1
cust 1
id 2
cust 1
id 3
cust 1

id 4
cust 2
id 5
cust 2
id 5
cust 2

I want the records
id 3
cust 1

and

id 5
cust 2

So I cannot simply do a select Max()...

Any ideas?

I have tried grouping on the report but the labels do not fill out correctly...

 

[Charliy]

You could use subreports to go get the highest numbered ID.

Grouping should have worked. Group on the Customer number and put _everything_ on the group footer.

 

[NewB2007]

When I put everything on the group footer the labels don't fill out properly.

I want my labels to look like this

cust1 name cust2 name cust3 name
add 1 add 1 add 1
add 2 add 2 add 2
city st zip city st sip city st zip

Then they go for 10 rows. I have to have this to match the type of labels that I am using.

When I put everything in a group I get 1 column and then multiple rows.

 

[lbass]

You should be able to insert a group on a customer ID field (and suppress both the header and footer), and then format each detail section to suppress with this formula:

{table.customerID} = previous({table.customerID})

-LB

 

[NewB2007]

Figured it out before you posted but that is exactly what I did! Thanks anyways!