Count of Objects with Related Objects 1

[mychal]

I have two tables, MAIN and ASSOCIATED. MAIN contains three fields: id, name, and location. ASSOCIATED contains two fields: id_1 and id_2. Both of these fields contain id's from MAIN. In other words, records from MAIN can be related to each other through the ASSOCIATED table.

I want to see how many records in MAIN have related records in MAIN. I also want to see how many total records there are for each location, and the number of records with related records per location.

I'm having trouble with this. Can anybody help? What do I group by? Location first and then id from the left hand version of MAIN?

Thanks in advance.

 

[Madawc]

Which Crystal? In 8.5, you could add MAIN again as an Alias. You can then link as if it were anotehr table.

Madawc Williams (East Anglia)

 

[mychal]

I'm guilty of not giving enough information. CR 8.5.

I have no problem with the linking, but I'm having trouble counting the number of records from MAIN that have cooresponding records in MAIN_1, and displaying that information in the Report Header.

For example, if MAIN contained three records:

1, 2, and 3

and ASSOCIATED contained:

(1,2), (1,3), and (2,3)

then the number of records in MAIN with a related record would be 2. Records 1 and 2 have related records. How can I determine that number and put it in the RH (without using sub-reports)?

Running totals work to get the number, but if they are present in the RH, they will not have calculated yet.

Sorry about the lack of detail.

 

[kskid]

You should have a link between ASSOCIATED.ID2 to MAIN_1.ID

Create Group1 on MAIN.ID
Insert Summary on MAIN_1.ID1 using distinct count

Cheers,

 

[mychal]

All the simple stuff, so far, but thanks for the help. How do I get the number of records with related records per location?

Thanks.

 

[lbass]

I think you could use a crosstab. You would need to have a left join from Main to Associated for this to work. Insert a crosstab, and use {Main.Location} for the row, and create a formula to use as the column:

if isnull({Associated.ID1}) then "No Related Records" else "Related Records"

Use a distinctcount of {Main.ID} as the summary field. This should show you a list by location of those IDs with and without related records, and a total at the bottom across all locations.

-LB