Subquery

[IceRuby]

Access 2007 Database, Crystal XI

Creating a report that requires a count of list of items e.g

Name Number
#Set 10
#Set1 12
#Partial 13
#Complete 20

Counts were initially counted in VB and then passed to Crystal. I would like to calc in Crystal.

The Report is to only take into account all distinct Id's in a date range that have Name = ‘Set’ so each calculation must effectively use this as a sub query to further filter data.

For example (VB)
Start = Select Count(Distinct ID) from table WHERE name = 'Set' AND in entered date range

Set1 = Select Count(Distinct ID) from LOG WHERE name = Set1 AND in entered date range AND ID IN (Select ID from Table WHERE name = 'Set' AND in entered date range )

I have created a group to create list headings (i.e. Set, Set1, Partial, Complete) however if I limited record selection to only Name = Set only the Set group is displayed. I still require the other groups to be displayed and if there is a count it is applied.

Appreciate thoughts on how this can be applied in crystal.

I seriously question the VB logic as only the same count of Set is returned.


Thanks in advance

 

[Madawc]

I don't know VB, but I think that if you select everything and then use a set of Running Totals with formuals to count, that should give you what you want. Also take a look at Crosstabs, they might do it with less work.

The use of Crystal's automated totals is outlined at FAQ767-6524.

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

 

[IceRuby]

Thanks. I resolved via creating new table containing only required detail and linked via inner join back to main table.