populating form recordset based on bitwise flags

[Guest]

I have a Users table, and each user can be a member of one or more groups. I
used a single integer Group field, and assigned a bit to each group they
could join (group1 = bit1, group2 = bit2, etc.) That way I can just use
bitwise operations on the field as they join or leave groups. Maybe this is
not a good approach, it seemed good at the time...

So, I have a Users form, where I want the operator to be able to select
which group's users they're looking at. I made a combo box with the groups in
it, but I can't figure out how to filter on the group. I was trying something
like: tval = Not GroupCombo.Value; Forms("Users").Filter = "[user_groups] =
[user_groups] | " & tVal, which works logically but not syntactically.

How should I be doing this? I'm open to a completely different approach if
necessary. The problem is that users can belong to more than one group...

Thanks,

Will

 

[Allen Browne]

Will, the bitfield is economical on disk space, but not simple to
manipulate. Better to use 3 tables:
User, with UserID primary key
Group, with GroupID primary key
UserGroup, with UserID and GroupID as foreign keys.
You end up with one record in UserGroup for every valid combination of user
and group.

If you want to proceed with the bitfield, you will need to parse the integer
with masks in Form_Current, and assign a series of unbound check boxes,
where Yes represents the bit is on, and No represents it is off. Then in the
AfterUpdate event of each check box, build the binary value and assign it to
the field so it gets saved. You also need to use Form_Undo to reset the
check boxes based on the OldValue of the integer field.

In VBA, you can use the And and Or operators for binary masking. In queries,
you can use BAND and BOR, but only in Access 2000 and later, and only if the
query is executed under ADO code (which means not in the query interface.)

Sounds like lots of work for something that could be done very easily with a
junction table to resolve the many-to-many relation.

 

[Guest]

Thanks, interesting reply. I'm not a long-time programmer, but not a career
dba, so I'm still learning many of these techniques.

So, with a separate table for users' groups as you suggested (I had
considered this approach), exactly how would you filter a form so you're only
seeing users who belong to a particular group when there are multiple group
records for some users? That's my main concern, not saving disk space. Any
technique is ok with me, vba, form filtering, pass-through query, whatever
works.

In my situation, the tables are ODBC-linked SQL tables, and the front-end is
an mdb, in case it matters to you.

Thanks,

Will

Will, the bitfield is economical on disk space, but not simple to
manipulate. Better to use 3 tables:
User, with UserID primary key
Group, with GroupID primary key
UserGroup, with UserID and GroupID as foreign keys.
You end up with one record in UserGroup for every valid combination of user
and group.

If you want to proceed with the bitfield, you will need to parse the integer
with masks in Form_Current, and assign a series of unbound check boxes,
where Yes represents the bit is on, and No represents it is off. Then in the
AfterUpdate event of each check box, build the binary value and assign it to
the field so it gets saved. You also need to use Form_Undo to reset the
check boxes based on the OldValue of the integer field.

In VBA, you can use the And and Or operators for binary masking. In queries,
you can use BAND and BOR, but only in Access 2000 and later, and only if the
query is executed under ADO code (which means not in the query interface.)

Sounds like lots of work for something that could be done very easily with a
junction table to resolve the many-to-many relation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a Users table, and each user can be a member of one or more groups.
I
used a single integer Group field, and assigned a bit to each group they
could join (group1 = bit1, group2 = bit2, etc.) That way I can just use
bitwise operations on the field as they join or leave groups. Maybe this
is
not a good approach, it seemed good at the time...

So, I have a Users form, where I want the operator to be able to select
which group's users they're looking at. I made a combo box with the groups
in
it, but I can't figure out how to filter on the group. I was trying
something
like: tval = Not GroupCombo.Value; Forms("Users").Filter = "[user_groups]
=
[user_groups] | " & tVal, which works logically but not syntactically.

How should I be doing this? I'm open to a completely different approach if
necessary. The problem is that users can belong to more than one group...

Thanks,

Will

 

[Allen Browne]

Typically the interface is a main form for the person (bound to User table),
with a subform for the groups they belong to (bound to the junction table
UserGroup). The continuous subform has a combo for selecting the group
(RowSource is the Group table), and you enter as many groups as you need in
the subform, one per row.

Then to filter the main form to only those users who belong to a particular
group, you can reassign its RecordSource to an inner join statement. Details
in:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, interesting reply. I'm not a long-time programmer, but not a
career
dba, so I'm still learning many of these techniques.

So, with a separate table for users' groups as you suggested (I had
considered this approach), exactly how would you filter a form so you're
only
seeing users who belong to a particular group when there are multiple
group
records for some users? That's my main concern, not saving disk space.
Any
technique is ok with me, vba, form filtering, pass-through query, whatever
works.

In my situation, the tables are ODBC-linked SQL tables, and the front-end
is
an mdb, in case it matters to you.

Thanks,

Will

Will, the bitfield is economical on disk space, but not simple to
manipulate. Better to use 3 tables:
User, with UserID primary key
Group, with GroupID primary key
UserGroup, with UserID and GroupID as foreign keys.
You end up with one record in UserGroup for every valid combination of
user
and group.

If you want to proceed with the bitfield, you will need to parse the
integer
with masks in Form_Current, and assign a series of unbound check boxes,
where Yes represents the bit is on, and No represents it is off. Then in
the
AfterUpdate event of each check box, build the binary value and assign it
to
the field so it gets saved. You also need to use Form_Undo to reset the
check boxes based on the OldValue of the integer field.

In VBA, you can use the And and Or operators for binary masking. In
queries,
you can use BAND and BOR, but only in Access 2000 and later, and only if
the
query is executed under ADO code (which means not in the query
interface.)

Sounds like lots of work for something that could be done very easily
with a
junction table to resolve the many-to-many relation.

I have a Users table, and each user can be a member of one or more
groups.
I
used a single integer Group field, and assigned a bit to each group
they
could join (group1 = bit1, group2 = bit2, etc.) That way I can just use
bitwise operations on the field as they join or leave groups. Maybe
this
is
not a good approach, it seemed good at the time...

So, I have a Users form, where I want the operator to be able to select
which group's users they're looking at. I made a combo box with the
groups
in
it, but I can't figure out how to filter on the group. I was trying
something
like: tval = Not GroupCombo.Value; Forms("Users").Filter =
"[user_groups]
=
[user_groups] | " & tVal, which works logically but not syntactically.

How should I be doing this? I'm open to a completely different approach
if
necessary. The problem is that users can belong to more than one
group...

 

[Guest]

Thanks Allen, works great. In my case I don't need the subform, but using a
3rd table with 2 foreign keys and the INNER JOIN is what I was needing. I
know you said that in your first post, but without the example I didn't get
it.

Will