Access 2003 - Join via calculated field...

[Xystren]

Hello All,

New member here, site looks great. I hope someone can provide some insight into my difficulties.

First off, I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.

So I have Table A:

MemberID (autonumber, Primary Key)
NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level
​

I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:

TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8
​

So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.

Table B: I want a list of "friendly name" for the different training levels.

TLID (Number, No duplicates)
TLFriendlyText (Text)
​

So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.

Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].

Does anyone have any thoughts on how best to acheive this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.

Thoughts?

Thanks in advance,
Xystren aka Greg

 

[MarkK]

Aren't those training levels in table A mutually exclusive? Like, if you're advanced, you are by definition not a beginner, so what I would do is merge those fields into one, and put those distinctions in a new table, like imagine a table called, tblTrainingLevels with data like . . .

TrainingLevelID LevelName
=============== =========
1               Beginner
2               Intermediate
3               Advanced

Now your table A, which you might call tblMember, can be simplified . . .

tblMember
MemberID (Autonumber, Primary Key)
TrainingLevelID (Foreign Key)
NameLast (Text)
NameFirst (Text)

. . . and in interfaces that show the training level ID, you use a ComboBox that shows data from the training table, and there is your friendly name.
hth

 

[Xystren]

Aren't those training levels in table A mutually exclusive? Like, if you're advanced, you are by definition not a beginner, so what I would do is merge those fields into one, and put those distinctions in a new table, like imagine a table called, tblTrainingLevels with data like . . .

TrainingLevelID LevelName
=============== =========
1               Beginner
2               Intermediate
3               Advanced

Now your table A, which you might call tblMember, can be simplified . . .

. . . and in interfaces that show the training level ID, you use a ComboBox that shows data from the training table, and there is your friendly name.
hth

Close, training level in this context is referring to the type of training that a member provides. Some will only provide fundamental training , others will provide intermediate only, other Advanced only, or some combination of the four different levels (continuing is for those that offer CEUs at particular levels. Just because someone is training (or perhaps teaching would be more clear) at the advanced level, doesn't necessary mean they also teach Fundamental, or Intermediate or Continuing - hence why I have the different yes/no fields. It would be nice if 3 by default also included 1 & 2, but the way this is setup, it is not the case.

Also the reasoning for going with the boolean, is as the administration/board of directors change, they like to "mark their territory" by changing the verbiage and or removing/adding different trainer levels. They used to have "Specialty" level, which was specifically different than "Advanced" <>

Hence, I'm kind of caught up in having to plan for the politics, and yet, try to maintain integrity with the fields. It was easy when they just had the three levels and the higher level would include the lower levels (as you described).

Hope that makes some sense and why the simple drop-down isn't really practical.

Cheers,
Greg

 

[MarkK]

Well, if one person teaches one or more courses then those courses should be in a different table from the person. One thing a database does awesome is model the reality of the one-to-many relationship, which is what you've just described. If you have the one thing related to many things in the same record, you've defeated the power of the relational model.

 

[Beetle]

Also the reasoning for going with the boolean, is as the administration/board of directors change, they like to "mark their territory" by changing the verbiage and or removing/adding different trainer levels. They used to have "Specialty" level, which was specifically different than "Advanced"

Actually, this is most definitely a reason not to use boolean fields to store this type of data. Think about it. Right now you have four boolean fields in your table, and you likely (or should) have other objects (queries, forms, reports) based on that table. Now a suit comes along and wants to "mark their territory" by adding (or removing) a training level, so you add (or remove) a boolean field in the table. Wait a minute....Damn! Now you have to go modify the design of every object that is based on that table!

If you do what Mark is suggesting, when a new training level needs to be added you simply add a new record to the TrainingLevel table. No need to modify the design of any related objects.