Board index » delphi » Getting record count of detail table

Jim Andrews

Delphi Developer
Fri, 05 Dec 2003 06:56:20 GMT

Getting record count of detail table

This should be easy but it has me stumped.

I want to have a calculated field in the master table with the record
count of the detail table for each master record.

When I try and use the oncalcfields event as follows:

procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
begin
  if DetailTable.Active then
    MasterTableCount.value := DetailTable.recordcount;
end;

The count shows as the count of the displayed record in the DBGrid - not
the count for the record being calculated.  It seems that the
OnCalcFields event happens without a lookup or other action to force a
change in the Master-Detail association

Ideas?

...Jim

 
M.H. Avegaar

Delphi Developer
Fri, 05 Dec 2003 22:33:55 GMT

Re:Getting record count of detail table


Correct, you'll need an additinal TQuery to do the counting, e.g.:

DetailCountQuery.SQL.Text :=
  'SELECT COUNT(*) FROM Detail WHERE Detail.KeyField=:KeyField';

procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
begin
  DetailCountQuery.ParamByName('KeyField').Assign(
    DataSet.FieldByName('KeyField');
  DetailCountQuery.Open;
  try
    DataSet.FieldByName('Count').AsInteger :=
DetailCountQuery.Fields[0].AsInteger;
  finally
    DetailCountQuery.Open;
  end;
end;

"Jim Andrews" <j...@azdogs.com> schreef in bericht
news:3B2D3594.68768638@azdogs.com...

Quote
> This should be easy but it has me stumped.

> I want to have a calculated field in the master table with the record
> count of the detail table for each master record.

> When I try and use the oncalcfields event as follows:

> procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
> begin
>   if DetailTable.Active then
>     MasterTableCount.value := DetailTable.recordcount;
> end;

> The count shows as the count of the displayed record in the DBGrid - not
> the count for the record being calculated.  It seems that the
> OnCalcFields event happens without a lookup or other action to force a
> change in the Master-Detail association

> Ideas?

> ...Jim

Jim Andrew

Delphi Developer
Sun, 07 Dec 2003 13:04:05 GMT

Re:Getting record count of detail table


Thanks.  I was hoping for a way not to spend the extra time required for a
query.

In the finally statement did you mean close rather than open?
  finally
    DetailCountQuery.Open;
  end;
end;

Is it faster if the query has less than all of the fields?

Will a prepare speed it up?

...Jim

Quote
"M.H. Avegaart" wrote:
> Correct, you'll need an additinal TQuery to do the counting, e.g.:

> DetailCountQuery.SQL.Text :=
>   'SELECT COUNT(*) FROM Detail WHERE Detail.KeyField=:KeyField';

> procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
> begin
>   DetailCountQuery.ParamByName('KeyField').Assign(
>     DataSet.FieldByName('KeyField');
>   DetailCountQuery.Open;
>   try
>     DataSet.FieldByName('Count').AsInteger :=
> DetailCountQuery.Fields[0].AsInteger;
>   finally
>     DetailCountQuery.Open;
>   end;
> end;

> "Jim Andrews" <j...@azdogs.com> schreef in bericht
> news:3B2D3594.68768638@azdogs.com...
> > This should be easy but it has me stumped.

> > I want to have a calculated field in the master table with the record
> > count of the detail table for each master record.

> > When I try and use the oncalcfields event as follows:

> > procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
> > begin
> >   if DetailTable.Active then
> >     MasterTableCount.value := DetailTable.recordcount;
> > end;

> > The count shows as the count of the displayed record in the DBGrid - not
> > the count for the record being calculated.  It seems that the
> > OnCalcFields event happens without a lookup or other action to force a
> > change in the Master-Detail association

> > Ideas?

> > ...Jim

M.H. Avegaar

Delphi Developer
Sun, 07 Dec 2003 16:12:59 GMT

Re:Getting record count of detail table


"Jim Andrews" <j...@azdogs.com> schreef in bericht
news:3B302EC5.DE08E195@azdogs.com...

Quote
> Thanks.  I was hoping for a way not to spend the extra time required for a
> query.

> In the finally statement did you mean close rather than open?
>   finally
>     DetailCountQuery.Open;
>   end;
> end;

Yes, sorry for that...

Quote
> Is it faster if the query has less than all of the fields?

COUNT(*) doesn't retreive all fields (AFAIK), but you can try it
COUNT(SingleFieldName) is faster.

Quote
> Will a prepare speed it up?

Yes.

Quote
> ...Jim

> "M.H. Avegaart" wrote:

> > Correct, you'll need an additinal TQuery to do the counting, e.g.:

> > DetailCountQuery.SQL.Text :=
> >   'SELECT COUNT(*) FROM Detail WHERE Detail.KeyField=:KeyField';

> > procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
> > begin
> >   DetailCountQuery.ParamByName('KeyField').Assign(
> >     DataSet.FieldByName('KeyField');
> >   DetailCountQuery.Open;
> >   try
> >     DataSet.FieldByName('Count').AsInteger :=
> > DetailCountQuery.Fields[0].AsInteger;
> >   finally
> >     DetailCountQuery.Open;
> >   end;
> > end;

> > "Jim Andrews" <j...@azdogs.com> schreef in bericht
> > news:3B2D3594.68768638@azdogs.com...
> > > This should be easy but it has me stumped.

> > > I want to have a calculated field in the master table with the record
> > > count of the detail table for each master record.

> > > When I try and use the oncalcfields event as follows:

> > > procedure TDM.MasterTableCalcFields(DataSet: TDataSet);
> > > begin
> > >   if DetailTable.Active then
> > >     MasterTableCount.value := DetailTable.recordcount;
> > > end;

> > > The count shows as the count of the displayed record in the DBGrid -
not
> > > the count for the record being calculated.  It seems that the
> > > OnCalcFields event happens without a lookup or other action to force a
> > > change in the Master-Detail association

> > > Ideas?

> > > ...Jim

Sundial Services International, In

Delphi Developer
Sun, 07 Dec 2003 21:09:18 GMT

Re:Getting record count of detail table


Quote
In article <3B302EC5.DE08E...@azdogs.com>, Jim Andrews <j...@azdogs.com> wrote:
> Thanks.  I was hoping for a way not to spend the extra time required for a
> query.

If all you need is the count, then use a query that returns the count to you.

If you need to display the records, then go ahead and use a query that
retrieves them, store the records temporarily in memory somehow, and count
them as you go.

Don't overlook in-memory storage of the returned data ... any workstation
you can name has enough power to run a small city if used properly.  (We
ran a large university on much less power than this.)  It's got copious
memory and disk storage now, right in front of the user's face.  If
bandwidth allows (network traffic needed to get all those records over the
wire...) then grab it down and hold it locally.

Quote

> In the finally statement did you mean close rather than open?
>   finally
>     DetailCountQuery.Open;
>   end;
> end;

> Is it faster if the query has less than all of the fields?

> Will a prepare speed it up?

Other Threads

1. Detail Table, Filtered & Record Count

2. Figure out record count in the Detail side of a Master/Detail setup

3. How to get Record Count of a Detail Record in DBGrid

4. existence of detail record in detail table

5. Problem when inserting records in the detail table (master/detail relation)

6. Delete records from detail table (in master detail)

7. Counting Detail records with SQL or Recordcount

8. Counting Sub-Detail Records

9. getting the count of records in a filtered query

10. Getting a Record Count...