Grouping or records

[Guest]

I have a form that has a button that feeds an Append query to another table.
The person want to be able to create groups or records that can then be
subtotaled on a report. My issue is how do I create a group that will allow
this. I know how to handle the report side. The thing is there is not
always a common denominator for this grouping. I had thought of creating an
unbound field that would hold a value until a button is pushed called new
group, then it would assign the next sequential number for a new group. The
number of records for each group can vary from 1 record and up. I can't
figure how to hoild the value then create a new value after the button is
pushed.

TIA

 

[Marshall Barton]

I have a form that has a button that feeds an Append query to another table.
The person want to be able to create groups or records that can then be
subtotaled on a report. My issue is how do I create a group that will allow
this. I know how to handle the report side. The thing is there is not
always a common denominator for this grouping. I had thought of creating an
unbound field that would hold a value until a button is pushed called new
group, then it would assign the next sequential number for a new group. The
number of records for each group can vary from 1 record and up. I can't
figure how to hoild the value then create a new value after the button is
pushed.

If your table has a field for the group number, getting the
next one is usually just a matter of looking up the biggest
one in the table and adding 1 to it:
newnum = Nz(DMax("grpnum","thetable"), 0) + 1

That may be inadequate if multiple users can be doing this
at the same time, so the lookup should be delayed until you
are ready to save the new number back to the table.

How do you identify which records are to be assigned the new
group number?

 

[Guest]

Basically the records are id'd by the person that is entering the data.
There is no steadfast rule other than the person reding info sent by the
customer or pulled from our records. This is being used for an accounting
function to take down line entries in a trial balance, and the rep wants to
be able to group records together for ease of reconciliation of the amounts.

 

[Marshall Barton]

Basically the records are id'd by the person that is entering the data.
There is no steadfast rule other than the person reding info sent by the
customer or pulled from our records. This is being used for an accounting
function to take down line entries in a trial balance, and the rep wants to
be able to group records together for ease of reconciliation of the amounts.

Sure, but what is this adhoc thing that the users do? You
can not run an UPDATE query to set the group id or even loop
through the form's recordset setting the group id without
having a field you can compare to something to determine
which records get the new group id.

If the users are just winging it according to their own
devices, then you need a button or something for them to
tell you to set the new group id into the current record.
Because of potential time lags, this could get tricky in a
multiuser environment. Perhaps you could do something hokey
like set the group id to a users id code and update all the
records when they say they're done adding records to a new
group. A different and near foolproof way is to create a
new one row, one field table that has the next available
group id. With this, you open a recordset in exclusive
mode, retrieve the value from the table, immediately add 1
to it and write it back to the table (closing the recordset
will release the exclusive lock so other users can get
another value). If that's done quickly enough, the odds of
another user being locked out are minimal and additional
code to retry would deal with that rare (if ever)
circumstance.

Because a user may indicate (somehow?) that they want to
create a new group (so you grab the next available number)
and then change their mind, the group ids across the entire
table will probably have some gaps in the number sequence.
There is very little that can be done about this. Besides,
who cares wich number identifies a group?

You will also want another mechanism that allows users to
add and remove records from an existing group.