Hi all,

I am trying to provide an indicator on a form to highlight every 10th
occurrence of a Formulation, so this Batch must be sent for testing. I
have already provided a query grouping the relevant Formulations
together.

Now I want to put a sequenced count, Counter, on this query as
follows:

Formulation Counter Batch No
A 1 B321
B 1 B127
B 2 B223
B 3 B234
: : :
: : :
B 9 B665
B 10 B765
C 1 B123 ETC.

One possibility is to set up a table called NextCounter with 10
records, 1 to 10. Then associate this table with the query such that
the query searches through the NextCounter and on reaching the 10th
value returns to 1st value. This is all very well in theory, but am
not aware of the code needed to do this.

Can anyone suggest an existing function or method to provide this
counter?

Thanks in advance,
Julie

Julie:
This is not quite as simple as it sounds.
It requires a function.
I also used a hidden form.
There are other ways to do this without the hidden form but I have
always found them to be the most versitile way to deal with persistant
values/
And for reasons that are, at the moment, beyond me, the query needs to
be a make table query.
If you send me your e-mail I'll send you a db that does this.
isolomon (AT) solomonltd (DOT) com
Good Luck
Ira Solomon

On 10 Nov 2003 03:13:17 -0800, juliebannon (AT) yahoo (DOT) co.uk (Julie) wrote:

I think you can easily use a Mod function for this - Mod of 10 - if
there's no remainder then it's the 10th item (or every 10th
item).....I think the function is just Mod.....

juliebannon (AT) yahoo (DOT) co.uk (Julie) wrote in message news:<d528d765.0311100313.2ffff12c (AT) posting (DOT) google.com>...

The difficulty was in actually inserting a "counter" to distinguish
the number per group. There after i can easily extract any Mod10=0 .
However I managed to find code in SQL for this:

SELECT j1.TrimVenla, COUNT(*) AS line_number
FROM [SELECT id, QueryFormulations.TrimVenla FROM QueryFormulations
GROUP BY id, QueryFormulations.TrimVenla]. AS j1 INNER JOIN
[SELECT id, QueryFormulations.TrimVenla FROM QueryFormulations
GROUP BY id, QueryFormulations.TrimVenla]. AS j2 ON (j1.id >=
j2.id) AND (j1.QueryFormulations.TrimVenla =
j2.QueryFormulations.TrimVenla)
GROUP BY j1.QueryFormulations.TrimVenla, j1.id
ORDER BY j1.QueryFormulations.TrimVenla, COUNT(*);

This works partly but I still have an issue trying to add in the third
required field, Batch No. I need to identify the 10th Batch No to be
sent for testing.

Any feedback on this is greatly appreciated.
Regards,
Julie

MeadeR (AT) ComputerSOSNJ (DOT) com (MeadeR) wrote in message news:<32684913.0311101130.f7549e8 (AT) posting (DOT) google.com>...

Hi Folks,

Got it working at last. If anyone wants to know code is:

SELECT j1.id, j1.TrimVenla, COUNT(*) AS line_number
FROM [SELECT id, QueryFormulations.TrimVenla FROM QueryFormulations
GROUP BY id, QueryFormulations.TrimVenla]. AS j1 INNER JOIN
[SELECT id, QueryFormulations.TrimVenla FROM QueryFormulations
GROUP BY id, QueryFormulations.TrimVenla]. AS j2 ON (j1.id >=
j2.id) AND (j1.QueryFormulations.TrimVenla =
j2.QueryFormulations.TrimVenla)
GROUP BY j1.QueryFormulations.TrimVenla, j1.id
ORDER BY j1.QueryFormulations.TrimVenla, COUNT(*);

So id = Batch No and all is working fine now.

Thanks for the input.

Regards,
Julie

juliebannon (AT) yahoo (DOT) co.uk (Julie) wrote in message news:<d528d765.0311110647.49b146a2 (AT) posting (DOT) google.com>...

Why not create a 'temp' table - one that you delete just prior to
using and that contains a autocounter in it. This would simplify the
query and allow a potential new field to indicate the 10th
record.......

juliebannon (AT) yahoo (DOT) co.uk (Julie) wrote in message news:<d528d765.0311110647.49b146a2 (AT) posting (DOT) google.com>...