Avoiding multiple queries

[Peter Hallett]

I have a table which consists of a series of records, each identified by a
unique ID. In addition to the ID field, each record comprises a series of
Boolean fields named, for the sake of convenience, A, B, C, D …. M …. For
each ID, one, and only one, of the latter fields is True. The task, for a
given ID, is to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName, “qry_Find_True_Fieldâ€)

where stFieldName is programmatically and sequentially set to “Aâ€, “Bâ€,
“C†… “M†... and the relevant ID is passed as a criterion to the query.
The code is run within a simple loop which assigns the appropriate values in
turn to stFieldName and is terminated as soon as bytCount becomes non-zero,
leaving the required field identifier in stFieldName. The problem is to find
an appropriate form for the query. It is child’s play if a separate query is
used for each field name but that rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an alternative
construction? Changing the form of the table is one possibility but that
could introduce more difficulties than it resolved.

 

[Peter Hallett]

I have a table which consists of a series of records, each identified by a
unique ID. In addition to the ID field, each record comprises a series of
Boolean fields named, for the sake of convenience, A, B, C, D …. M …. For
each ID, one, and only one, of the latter fields is True. The task, for a
given ID, is to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName, “qry_Find_True_Fieldâ€)

where stFieldName is programmatically and sequentially set to “Aâ€, “Bâ€,
“C†… “M†... and the relevant ID is passed as a criterion to the query.
The code is run within a simple loop which assigns the appropriate values in
turn to stFieldName and is terminated as soon as bytCount becomes non-zero,
leaving the required field identifier in stFieldName. The problem is to find
an appropriate form for the query. It is child’s play if a separate query is
used for each field name but that rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an alternative
construction? Changing the form of the table is one possibility but that
could introduce more difficulties than it resolved.

I once worked with a man who kept a large teddy bear in the corner of his
office. Asked the purpose of the creature he replied, “If I have a problem
then I explain it to the teddy bear. He is not the brightest of creatures so
I have to spell it out in detail. By the time I have finished, I have
usually worked out the answer.â€

Today, the Discussion Group was my teddy bear. About half an hour after
submitting my question the answer became obvious. The solution is, of
course, quite trivial.

 

[Albert D. Kallal]

Today, the Discussion Group was my teddy bear. About half an hour after
submitting my question the answer became obvious. The solution is, of
course, quite trivial.

And you are sharing that trival solution with us???

 

[John W. Vinson]

Today, the Discussion Group was my teddy bear. About half an hour after
submitting my question the answer became obvious. The solution is, of
course, quite trivial.

The teddy bear has fangs today...

Your table design IS WRONG. If you have a many (main table) to many (yes/no
decisions) relationship you should not store data in fieldnames of your yes/no
fields, you should have three tables representing the many to many
relationship.

GRRR!!!!

 

[Peter Hallett]

Albert,

I append the code below but you must promise not to show it to John Vinson.
I did not send the solution with my last post because I thought everyone
would say, “Yes, that’s obvious. Why didn’t you spot it earlier?†(Note
that the following is just the basic skeleton. In its complete version, the
sub takes a form as argument, which I have left redundantly in place, and
uses this to set a number of controls on that form.)

Public Sub Get_Display_Group(InForm As Form)
‘This sub is called with the member’s ID in a public long variable named X_ID.
‘X_Group is a public Boolean variable.
Dim stFieldName As String
Dim bytRecordCount As Byte
Dim ACGroup As Variant
Dim J As Byte

bytRecordCount = DCount("[Group_ID]", "qry_Check_Group_Membership")
‘qry_Check_Group_Membership uses X_ID as a criterion to check the list
of
‘ID’s in tbl_Group_Members to see whether the current member belongs to
a Group.
X_Group = (bytRecordCount > 0)
If X_Group Then
ACGroup = Array("A", "B", "C", "D", "E", "F", "X")
For J = 0 To GroupNum
'GroupNum is a public constant defining the number of groups.
Y_Group(0) = ACGroup(J)
‘Y_Group() is a public array. The current Group identifier is held in the
‘first element.
stFieldName = "[Group " & Y_Group(0) & "]"
‘The actual field names in tbl_Group_Members, are Group_ID,
‘Group A, Group B, Group C, …, Group F and Group X
X_Group = DLookup(stFieldName, "qry_Find_Members_Group")
If X_Group Then Exit For
Next
End If
End Sub

On return from the above sub, X_Group indicates whether the current member
belongs to a Group. If so, the appropriate Group identifier is located in
Y_Group(0).

There are obviously numerous different ways of doing the job. Purists,
particularly of the structured programming variety, might favour less use of
public variables. Admittedly, they have their dangers and argument passing
might be preferred but the values determined in the first call to this
sub-routine are used in a string of subsequent forms. The risk of accidental
corruption is felt to be less than the bother of having to repeatedly
re-evaluate the variables. You might, though, see an easier way of doing it

 

[Peter Hallett]

In the following, I failed to clarify the nature of qry_Find_Members_Group.
It simply returns all the Group field values for the nominated ID. It is
therefore the current value of stFieldName which determines which particular
value is read by X_Group.

Albert,

I append the code below but you must promise not to show it to John Vinson.
I did not send the solution with my last post because I thought everyone
would say, “Yes, that’s obvious. Why didn’t you spot it earlier?†(Note
that the following is just the basic skeleton. In its complete version, the
sub takes a form as argument, which I have left redundantly in place, and
uses this to set a number of controls on that form.)

Public Sub Get_Display_Group(InForm As Form)
‘This sub is called with the member’s ID in a public long variable named X_ID.
‘X_Group is a public Boolean variable.
Dim stFieldName As String
Dim bytRecordCount As Byte
Dim ACGroup As Variant
Dim J As Byte

bytRecordCount = DCount("[Group_ID]", "qry_Check_Group_Membership")
‘qry_Check_Group_Membership uses X_ID as a criterion to check the list
of
‘ID’s in tbl_Group_Members to see whether the current member belongs to
a Group.
X_Group = (bytRecordCount > 0)
If X_Group Then
ACGroup = Array("A", "B", "C", "D", "E", "F", "X")
For J = 0 To GroupNum
'GroupNum is a public constant defining the number of groups.
Y_Group(0) = ACGroup(J)
‘Y_Group() is a public array. The current Group identifier is held in the
‘first element.
stFieldName = "[Group " & Y_Group(0) & "]"
‘The actual field names in tbl_Group_Members, are Group_ID,
‘Group A, Group B, Group C, …, Group F and Group X
X_Group = DLookup(stFieldName, "qry_Find_Members_Group")
If X_Group Then Exit For
Next
End If
End Sub

On return from the above sub, X_Group indicates whether the current member
belongs to a Group. If so, the appropriate Group identifier is located in
Y_Group(0).

There are obviously numerous different ways of doing the job. Purists,
particularly of the structured programming variety, might favour less use of
public variables. Admittedly, they have their dangers and argument passing
might be preferred but the values determined in the first call to this
sub-routine are used in a string of subsequent forms. The risk of accidental
corruption is felt to be less than the bother of having to repeatedly
re-evaluate the variables. You might, though, see an easier way of doing it

And you are sharing that trival solution with us???

 

[John W. Vinson]

I append the code below but you must promise not to show it to John Vinson.

Absolvo te, filius meus. Go and sin no more.

<g>

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have a table which consists of a series of records, each
identified by a unique ID. In addition to the ID field, each
record comprises a series of Boolean fields named, for the sake of
convenience, A, B, C, D …. M …. For each ID, one, and only
one, of the latter fields is True. The task, for a given ID, is
to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName, “qry_Find_True_Fieldâ€)

where stFieldName is programmatically and sequentially set to
“Aâ€, “Bâ€, “C†… “M†... and the relevant ID
is passed as a criterion to the query. The code is run within a
simple loop which assigns the appropriate values in turn to
stFieldName and is terminated as soon as bytCount becomes
non-zero, leaving the required field identifier in stFieldName.
The problem is to find an appropriate form for the query. It is
child’s play if a separate query is used for each field name but
that rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an
alternative construction? Changing the form of the table is one
possibility but that could introduce more difficulties than it
resolved.

Since booleans have a 0 value when false and -1 for true, you can use
abs([A]*1+*2+[C]*4+[D]*8) as a calculated field in the query.
This will also easily allow a test for 2 or more checkboxes assigned
to the same ID, or a record with none set.

 

[David W. Fenton]

And you are sharing that trival solution with us???

About 3/4s of the posts I start end up never getting posted, because
the process of writing them up for the newsgroup (and boiling the
problem down to its essentials) causes me to discover the solution.

I wonder how many of those solutions would have been useful to
others? No way to know, I guess.

 

[Gina Whipp]

You know David, that happens to me too! And you are right but if I spent my
time doing that when would I get an work done ;-)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Peter Hallett]

Yes, that could work well, turning the job from a For ... Next loop into
something much closer to a one-liner, with obvious run-time benefits,
particularly when interrogating a big table.

Being based on a geometric series, the results of the summation are, of
course, representable as binary numbers and are therefore unique, even if
more than one box is ticked, which could become a possibility in the near
future.

The price to pay, however, seems to lie in the interpretation of the result.
A Select Case clause would appear to be needed and, with multiple boxes
ticked, this might not look too pretty. Nevertheless, with the table
currently small and uncomplicated I think I'll give your solution a go. It
will enable me to kill several birds with just one stone.
I'll add an additional field to the table and pass it the 'loaded sum' of
the 'ticks', for the record being interrogated, using an update query. All
that is then needed is a DLookup to read the result.
Thanks for that one.

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have a table which consists of a series of records, each
identified by a unique ID. In addition to the ID field, each
record comprises a series of Boolean fields named, for the sake of
convenience, A, B, C, D …. M …. For each ID, one, and only
one, of the latter fields is True. The task, for a given ID, is
to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName, “qry_Find_True_Fieldâ€Â)

where stFieldName is programmatically and sequentially set to
“Aâ€Â, “Bâ€Â, “C†… “M†... and the relevant ID
is passed as a criterion to the query. The code is run within a
simple loop which assigns the appropriate values in turn to
stFieldName and is terminated as soon as bytCount becomes
non-zero, leaving the required field identifier in stFieldName.
The problem is to find an appropriate form for the query. It is
child’s play if a separate query is used for each field name but
that rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an
alternative construction? Changing the form of the table is one
possibility but that could introduce more difficulties than it
resolved.

Since booleans have a 0 value when false and -1 for true, you can use
abs([A]*1+*2+[C]*4+[D]*8) as a calculated field in the query.
This will also easily allow a test for 2 or more checkboxes assigned
to the same ID, or a record with none set.

 

[Peter Hallett]

The teddy bear has fangs today...

Your table design IS WRONG. If you have a many (main table) to many (yes/no
decisions) relationship you should not store data in fieldnames of your yes/no
fields, you should have three tables representing the many to many
relationship.

GRRR!!!!

Well, I have just checked my teddy bear and I have to tell you that, unlike
you, he hasn’t got a tooth in his head! (He is very old.)

I could redesign my a table to consist of just two fields, namely members’
IDs and the associated Group identifiers, A, B, C, …,F, or X. That would
meet your onene ideal and could be made to work but it is likely that
members will soon be allowed to join more than one group. The onene table
then again becomes one:many. I can’t immediately see a solution which still
meets your criteria. In the mean time, all I can say is that, with the
application of a bit of ingenuity the thing appears to work well as it is.
Will I be accused of iconoclasm if I continue to take the view, “If it ain’t
bust, why fix it?�

I have shown your second reply to the teddy bear, who said, “Better watch
this guy. He sounds like a Jesuit to me. I’d check whether he is offering
you unconditional absolution or just temporary forgiveness. You never know
with those chaps. Your mortal soul could still be in danger.†I wouldn’t
take too much notice of him, though. His theology is no better than his VBA
– and that’s rubbish.

 

[Peter Hallett]

I have just implemented your solution, Bob, and it works a treat. Since it's
'once-through' it's fast (not that the For ... Next version was slow) but the
code is a little prolix, as expected. While the table remains small, that
matters little, but the speed/prolixity relationship might be more difficult
to balance with a big table featuring a one:many ID/Selected-Group
relationship. I'll reassess the situation if or when the need arises but
that certainly won't be for a while yet.

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have a table which consists of a series of records, each
identified by a unique ID. In addition to the ID field, each
record comprises a series of Boolean fields named, for the sake of
convenience, A, B, C, D …. M …. For each ID, one, and only
one, of the latter fields is True. The task, for a given ID, is
to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName, “qry_Find_True_Fieldâ€Â)

where stFieldName is programmatically and sequentially set to
“Aâ€Â, “Bâ€Â, “C†… “M†... and the relevant ID
is passed as a criterion to the query. The code is run within a
simple loop which assigns the appropriate values in turn to
stFieldName and is terminated as soon as bytCount becomes
non-zero, leaving the required field identifier in stFieldName.
The problem is to find an appropriate form for the query. It is
child’s play if a separate query is used for each field name but
that rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an
alternative construction? Changing the form of the table is one
possibility but that could introduce more difficulties than it
resolved.

Since booleans have a 0 value when false and -1 for true, you can use
abs([A]*1+*2+[C]*4+[D]*8) as a calculated field in the query.
This will also easily allow a test for 2 or more checkboxes assigned
to the same ID, or a record with none set.

 

[John W. Vinson]

I could redesign my a table to consist of just two fields, namely members’
IDs and the associated Group identifiers, A, B, C, …,F, or X. That would
meet your onene ideal and could be made to work but it is likely that
members will soon be allowed to join more than one group. The onene table
then again becomes one:many. I can’t immediately see a solution which still
meets your criteria. In the mean time, all I can say is that, with the
application of a bit of ingenuity the thing appears to work well as it is.
Will I be accused of iconoclasm if I continue to take the view, “If it ain’t
bust, why fix it?”?

The problem is that if you need to add a new group you then will need to
restructure your table... rebuild your form... rewrite all your queries...
rewrite all your code.

You have a perfectly plain-vanilla many to many relationship. Each Member may
be in zero, one, or many Groups. Each Group may have zero, one or many
Members. Perhaps you're only using one group at present but you indicate that
this may change.

I'd really recommend a three table solution:

Members
MemberID
LastName
<etc etc>

Groups
GroupID <primary key, could be text e.g. A, B, ..., X if that's useful>
Description

Membership
GroupID
MemberID

(p.s. I'm a Presbyterian but I've hung around with Jesuits <g>)

 

[Peter Hallett]

Yes, that might be worth another look. At the moment things are stable and
one or two spare groups have been provided but you know, as well as I, how
quickly things alter and I am aware that any substantial modifications to the
structure could entail a fair bit of work in the areas you indicate. The
rule of thumb seems to be that if a DBMS is not being changed then it is not
being used. It is the main reason why I discourage users from wasting time
writing detailed project specifications. They are usually irrelevant before
the ink has had chance to dry, and not infrequently it is the declared
immutable elements that are the earliest casualties.

The first of your suggested tables already exists in exactly the form you
outline. Another existing table can be relatively easily modified to yield
your second table and the third needs to be created.

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

Yes, that could work well, turning the job from a For ... Next
loop into something much closer to a one-liner, with obvious
run-time benefits, particularly when interrogating a big table.

Being based on a geometric series, the results of the summation
are, of course, representable as binary numbers and are therefore
unique, even if more than one box is ticked, which could become a
possibility in the near future.

The price to pay, however, seems to lie in the interpretation of
the result.
A Select Case clause would appear to be needed and, with multiple
boxes
ticked, this might not look too pretty.

A select case would be very simple, SELEC Case 3, handles the first
two selected, select case 15 handles all four. Nice and neatly
ordered...
Alternatively an array, with the subscripts matching the calculated
value, could be used.

I'd even suggest a small table, with the numbre as primary key, could
be referenced via DLookup.

Nevertheless, with the

table currently small and uncomplicated I think I'll give your
solution a go. It will enable me to kill several birds with just
one stone.

I'll add an additional field to the table and pass it

the 'loaded sum' of the 'ticks', for the record being
interrogated, using an update query.

No, please don't add a field. Base the dlookup() on the query
instead. Storing what should be calculated in real time is an
invitation to problems caused by that field getting un-synchronized
from the main field.

Actually, you may be better to add the field, update the values, then
kill the booleans. That way the database can expand without needing a
redesign. In your forms and reports you might be able to use an
option group type display....


All that is then needed is a

DLookup to read the result. Thanks for that one.

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have a table which consists of a series of records, each
identified by a unique ID. In addition to the ID field, each
record comprises a series of Boolean fields named, for the sake
of convenience, A, B, C, D …. M …. For each ID,
one, and only one, of the latter fields is True. The task, for
a given ID, is to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName,
“qry_Find_True_Fieldâ€Â)

where stFieldName is programmatically and sequentially set to
“Aâ€Â, “Bâ€Â, “C†…
“M†... and the relevant ID is passed as a
criterion to the query. The code is run within a simple loop
which assigns the appropriate values in turn to stFieldName and
is terminated as soon as bytCount becomes non-zero, leaving the
required field identifier in stFieldName. The problem is to
find an appropriate form for the query. It is child’s
play if a separate query is used for each field name but that
rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an
alternative construction? Changing the form of the table is
one possibility but that could introduce more difficulties than
it resolved.

Since booleans have a 0 value when false and -1 for true, you can
use abs([A]*1+*2+[C]*4+[D]*8) as a calculated field in the
query. This will also easily allow a test for 2 or more
checkboxes assigned to the same ID, or a record with none set.

 

[Bob Quintal]

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have just implemented your solution, Bob, and it works a treat.
Since it's 'once-through' it's fast (not that the For ... Next
version was slow) but the code is a little prolix, as expected.
While the table remains small, that matters little, but the
speed/prolixity relationship might be more difficult to balance
with a big table featuring a one:many ID/Selected-Group
relationship. I'll reassess the situation if or when the need
arises but that certainly won't be for a while yet.

See my response to your other post and John Vinson's reply to you.

If you get the design right at this point in time, you'll be much
less frustrated later.

=?Utf-8?B?UGV0ZXIgSGFsbGV0dA==?=

I have a table which consists of a series of records, each
identified by a unique ID. In addition to the ID field, each
record comprises a series of Boolean fields named, for the sake
of convenience, A, B, C, D …. M …. For each ID,
one, and only one, of the latter fields is True. The task, for
a given ID, is to identify the True field.

An apparently simply solution has the form:-

bytCount = DCount(stFieldName,
“qry_Find_True_Fieldâ€Â)

where stFieldName is programmatically and sequentially set to
“Aâ€Â, “Bâ€Â, “C†…
“M†... and the relevant ID is passed as a
criterion to the query. The code is run within a simple loop
which assigns the appropriate values in turn to stFieldName and
is terminated as soon as bytCount becomes non-zero, leaving the
required field identifier in stFieldName. The problem is to
find an appropriate form for the query. It is child’s
play if a separate query is used for each field name but that
rapidly gets unwieldy.

Can anyone suggest a single query that would do the job or an
alternative construction? Changing the form of the table is
one possibility but that could introduce more difficulties than
it resolved.

Since booleans have a 0 value when false and -1 for true, you can
use abs([A]*1+*2+[C]*4+[D]*8) as a calculated field in the
query. This will also easily allow a test for 2 or more
checkboxes assigned to the same ID, or a record with none set.