Producing statistics - Opinions please

[JethroUK©]

i need to email weekly report of statistic from my database

i could find last week (>Now - 7), but i'm inclined to store the last time i
sent stats incase they are not sent exactly on time and find occurances
since [last stats] upto now

tricky part is collating these stats (i.e. how many new starts, completers,
acheivments (how many did x, y & z this week), next start date) - no client
details, just a matter of counting really & all quite easy to find on their
own but they all need different queries which need consolidating onto one
page report

i was thinking about creating a simple report and using DCount func to
derive each figure - but i think Dcount is restricted to criteria on the
field you're counting (in most cases i will have to find dates and count a
different field in that date range - Can Dcount handle this?

alternatively i will have to produce a nightmare query (consolidation of
many queries) to get all the totals i need

what do you think is the best/easiest way?

i dont mind coding & have even considered creating seperate recordset in vb
for each individual answer, but again i'm not sure whether i can do this on
the same report (reports and forms seem to be restricted to one recordset) -
can vb overcome this?

 

[John Spencer]

DCount and all the other domain functions can have criteria that apply to any
fields available in the table or base query.

DCOUNT("SomeField","SomeTable","SomeOTHERField= 'Allen' ")

That would give you a count of the number of records where somefield contained a
value and the SomeOtherField contained Allen.

There may be a more efficient method then using DCount, DSum, DAvg etc.;
however, without knowing a lot of detail about what you are doing and your table
structure I would have no idea what to recommend.

 

[JethroUK©]

In that case DCount sounds fine for my report - thanks very much for
confirming

DCount and all the other domain functions can have criteria that apply to any
fields available in the table or base query.

DCOUNT("SomeField","SomeTable","SomeOTHERField= 'Allen' ")

That would give you a count of the number of records where somefield contained a
value and the SomeOtherField contained Allen.

There may be a more efficient method then using DCount, DSum, DAvg etc.;
however, without knowing a lot of detail about what you are doing and your table
structure I would have no idea what to recommend.

i need to email weekly report of statistic from my database

i could find last week (>Now - 7), but i'm inclined to store the last time i
sent stats incase they are not sent exactly on time and find occurances
since [last stats] upto now

tricky part is collating these stats (i.e. how many new starts, completers,
acheivments (how many did x, y & z this week), next start date) - no client
details, just a matter of counting really & all quite easy to find on their
own but they all need different queries which need consolidating onto one
page report

i was thinking about creating a simple report and using DCount func to
derive each figure - but i think Dcount is restricted to criteria on the
field you're counting (in most cases i will have to find dates and count a
different field in that date range - Can Dcount handle this?

alternatively i will have to produce a nightmare query (consolidation of
many queries) to get all the totals i need

what do you think is the best/easiest way?

i dont mind coding & have even considered creating seperate recordset in vb
for each individual answer, but again i'm not sure whether i can do this on
the same report (reports and forms seem to be restricted to one recordset) -
can vb overcome this?

 

[Pat Hartman]

I think that you will find DCount() to be unpleasantly slow if your tables
contain more than a few hundred records. A far more efficient method is to
use queries that use the aggregate function Sum() or to Sum() in the report
itself.

Also, If you use Now() when you really mean Date(), you will get unexpected
results. Now() returns the current date AND time. Date() only returns the
current date. If your date fields actually include time, you will have
correct results if you eliminate time entirely from the expression:

Where DateValue(YourDate) > Date() - 7;

In that case DCount sounds fine for my report - thanks very much for
confirming

DCount and all the other domain functions can have criteria that apply to any
fields available in the table or base query.

DCOUNT("SomeField","SomeTable","SomeOTHERField= 'Allen' ")

That would give you a count of the number of records where somefield contained a
value and the SomeOtherField contained Allen.

There may be a more efficient method then using DCount, DSum, DAvg etc.;
however, without knowing a lot of detail about what you are doing and
your table
structure I would have no idea what to recommend.

i need to email weekly report of statistic from my database

i could find last week (>Now - 7), but i'm inclined to store the last time i
sent stats incase they are not sent exactly on time and find occurances
since [last stats] upto now

tricky part is collating these stats (i.e. how many new starts, completers,
acheivments (how many did x, y & z this week), next start date) - no client
details, just a matter of counting really & all quite easy to find on their
own but they all need different queries which need consolidating onto one
page report

i was thinking about creating a simple report and using DCount func to
derive each figure - but i think Dcount is restricted to criteria on
the
field you're counting (in most cases i will have to find dates and
count a
different field in that date range - Can Dcount handle this?

alternatively i will have to produce a nightmare query (consolidation
of
many queries) to get all the totals i need

what do you think is the best/easiest way?

i dont mind coding & have even considered creating seperate recordset
in vb
for each individual answer, but again i'm not sure whether i can do
this on
the same report (reports and forms seem to be restricted to one recordset) -
can vb overcome this?

 

[JethroUK©]

i'm hopeless with aggregate functions - read and re-read but i simply dont
understand them - apart from which i would need the results from at least 12
totally different queries - i know the Dcount method is very, very slow (and
i have to prodcue 70+ stats on a report) but they seem fairly easy to build
and the report is only generated once a week

i've taken on board the now vs date thing

I think that you will find DCount() to be unpleasantly slow if your tables
contain more than a few hundred records. A far more efficient method is to
use queries that use the aggregate function Sum() or to Sum() in the report
itself.

Also, If you use Now() when you really mean Date(), you will get unexpected
results. Now() returns the current date AND time. Date() only returns the
current date. If your date fields actually include time, you will have
correct results if you eliminate time entirely from the expression:

Where DateValue(YourDate) > Date() - 7;

In that case DCount sounds fine for my report - thanks very much for
confirming

DCount and all the other domain functions can have criteria that apply

to
any

fields available in the table or base query.

DCOUNT("SomeField","SomeTable","SomeOTHERField= 'Allen' ")

That would give you a count of the number of records where somefield contained a
value and the SomeOtherField contained Allen.

There may be a more efficient method then using DCount, DSum, DAvg etc.;
however, without knowing a lot of detail about what you are doing and
your table
structure I would have no idea what to recommend.

:

i need to email weekly report of statistic from my database

i could find last week (>Now - 7), but i'm inclined to store the last time i
sent stats incase they are not sent exactly on time and find occurances
since [last stats] upto now

tricky part is collating these stats (i.e. how many new starts, completers,
acheivments (how many did x, y & z this week), next start date) - no client
details, just a matter of counting really & all quite easy to find on their
own but they all need different queries which need consolidating onto one
page report

i was thinking about creating a simple report and using DCount func to
derive each figure - but i think Dcount is restricted to criteria on
the
field you're counting (in most cases i will have to find dates and
count a
different field in that date range - Can Dcount handle this?

alternatively i will have to produce a nightmare query (consolidation
of
many queries) to get all the totals i need

what do you think is the best/easiest way?

i dont mind coding & have even considered creating seperate recordset
in vb
for each individual answer, but again i'm not sure whether i can do
this on
the same report (reports and forms seem to be restricted to one recordset) -
can vb overcome this?