Using a value from one table to define which field to use

[JudyB]

Hi! I have a table with a lot of true/false fields to say which groups each
person is in (for example, the field "Group6" is true for people in that
group). I link to that table from a number of databases (one for each
group). I have a table in each little database with variables for that group
(GROUPINFOTABLE) - including the name of the true/false field for that group
(example fieldname = ThisGroupName; value = "Group6")

I know I can make make an expression
IIf([BIG_TABLE].[Group6]=True,"Member","NonMember")
and if I set the criteria for that field as "Member" I'll get the members of
Group6.

What I want to do is something along the lines of
IIF([BIG_TABLE]. & [ & value of GROUPINFOTABLE.ThisGroupName & ]
=True,"Member","NonMember")
and use that to get the members of the group.

That way if I make a copy of the database for a new group, I just have to
change the group name in one place, and all queries that use that name will
show the new group.

(I suspect if that was all I was doing, there'd be a better way - but I also
need to know who is in both group5 (defined as group6-1) and group6 and who's
in 6 but not 5 and so on - all of which I can do easily enough - if I can
find a way to use text I enter or calculate to provide the name of the field
in the big table that I want to use in my query.)

I do hope you can at least understand what I'm asking - and it would be
really great if you have an answer other than "it's impossible"! Thanks!

I have Access 2003.

 

[KARL DEWEY]

I suspect if that was all I was doing, there'd be a better way
The better way is to not use a spreadsheet method but a relational database.
tblPeople --
PeopID - autonumber - primary key
Lname - text
Fname - text
MI - text
etc.

tblMemberShip --
MemID - autonumber - primary key
Name - text -- maybe name it Group
Description - text
Dues - Number - single
Period - text - Monthly, Annual, None
Prerequisite - memo

tblPeopMem --
PeopMemID - autonumber - primary key
MemID - number - long integer - foreign key
PeopID - number - long integer - foreign key
Join - DateTime
Paid - Yes/No
Expire - DateTime --- might name it Renew

Set up one-to-many relationships between the primary key fields and foreign
key fields.
Use form/subform to enter/display.

 

[JudyB]

Thanks, Karl. I can see how that will let me make forms to display the data
by person or by group. I'm less sure how I can use that format to make the
query that underlies the report that has the biographical data for the
members of Group6. Part of that report is "...Member of Groups: Group2,
Group3, Group5, Group6." There's a new group elected ever 2 years and
people can't be in more than 4 consecutive groups - so I also need a report
that shows what the last group each person can be a member of (if first
elected to Group6, and continuously reelected, they'll be ineligible to run
for group10). Doing all that for any particular group is easy - the query
underlying the report that gives biographical data on those members of Group6
in their last term knows they are in their last term based on the following
IIF([Group3]=true And [Group4]=true And [Group5]=true And [Group6]=true,
"Last Term","Can Run Again")

When Group7 is elected, I can make a query to see who is in their last term
using
IIF([Group4]=true And [Group5]=true And [Group6]=true And [Group7]=true,
"Last Term","Can Run Again")
And I know I don't actually need the "=true" but it makes it clearer for me
to use it here.

But given that there'll be a new group every 2 years, if there was a way to
change the IIF expression to
CurrentGroup = Group7
IIF([CurrentGroup-3]=true And [CurrentGroup-2]=true And
[CurrentGroup-1]=true And [CurrentGroup]=true, "Last Term","Can Run Again")
Then all I'd need to do when I set up the database for a new group is
redefine CurrentGroup and all the reports of new members and returning
members and members in their last term and such would be correct. Otherwise,
I have to go into every query of that nature and change the expressions.

I do know that if CurrentGroup = "Group7" then CurrentGroup-1 won't
automatically be "Group6" - but I can write an expression to calculate that.
What I can't do is figure how to tell if someone is in Group7 without
actually having [Group7] in the expression.

By the way, the reason I have separate databases for each group (using
linked tables to the big database where tblPerson/BIG_TABLE and all the other
"permanent" data lives) is that most of the time, most of the people in the
office only need reports for the current group - and if someone needs mailing
labels for the group from 4 years ago or biographical data for the group 50
years ago, they can go to the database for that group to print it out,
without looking through thousands of reports in one huge database.

If I'm missing something in your suggestion, Karl, I do apologize. And
thanks!

 

[JudyB]

I'm not sure if Karl's post actually answered my question, or if there is no
way to do what I'm trying to do.

If Karl's post answered it, I'm sorry to be dense and I would love a hint as
to how it can help me avoid changing a lot of references in expressions every
two years.

If it's not possible, I guess I'll go back to my alternate plan of making a
rather long list of everything that needs to be changed.

Thanks!