Query question

[PerryG]

Is it possible within a query to display a count of items in a field that resets back to 1 when the field content changes? i.e.......field 3 is the counter field. I don't see how this can be done in a query, but I promised a user I'd ask. This isn't an application I support for the users.
field 1 field 2 field 3
abc 123 1
abc 456 2
abc 789 3
cba 123 1
cba 456 2
bca 123 1

Thanks for taking a look and any suggestions.

Perry

 

[Guest_imported]

Although Microsoft Access does use SQL to interrogate and aggregate data, it is unable to (as far as I know) interrogate datasets in a procedural type motion. For example a simple SELECT statement can return all records. Procedural SQL allows the use of variables, input prompts, modules, etc. This allows you to interrogate row(s) against other row(s), which is essentially what the user is asking for.

One option may be to use a reporting tool. I'm not sure if the Access reporting mechanism can interrogate the previous field however, you can get it to use "Groups" by which you could place a total count of "abc"'s and "cba"'s at the bottom of the group. I assume that these numbers are used just as a total count?