Query/Expression Assistance

[JerrodConaway]

I have created a query based on two akwardly designed tables that ultimately relate a member of Congress to a specific Committee and Subcommittee. Unfortunately I cannot alter the tables, as I receive weekly updates from an external source.

This being the case, once I have created the query, a "-" appears in the Subcommittee column, where the Member of Congress serves on the Committee, but no Subcommittee or the Committee does not have a Subcommittee. Although I could exclude the "-", however when I do this certain committees are removed from the final results. So I attempted creating an expression, where "<Not Assigned to a SubCommittee>" appears if the Committee does not have a SubCommittee. Unfortunately there are a few committees that fall under this. I created the following expression:

=IIf([HMC_Name]=("Committee on Budget""Committee on Standards of Official Conduct""Committee on House Administration"),"<No Subcommittee Assignment>",[HSC_Name])​

Yet this expression returns #Error. Any suggestions?

 

[Moniker]

=IIf([HMC_Name]="Committee on Budget" OR [HMC_Name]="Committee on Standards of Official Conduct" OR [HMC_Name]="Committee on House Administration","<No Subcommittee Assignment>",[HSC_Name])

 

[The_Doc_Man]

Well, your FIRST big problem is trying to make sense out of anything related to the United States Congress.

Moniker has offered a solution. I would think that a given congressperson would fall under one table, a committee would be a different table, and you would have a junction table between them, because congresspersons can serve on zero, one, or more than one committee or sub-committee. Look up Junction tables in this forum using the SEARCH option.

 

[JerrodConaway]

Moniker's response was a great way to force data, and it did help out to a certain degree. Unfortunately I am still experiencing problems with 36 of my entries (out of 322). With the remaining Congressmen, the problem essentially involves their status on a committee. For instance, if a Member is the chairman of a committee he does not serve on any Subcommittees, however if a Member is a Ranking Member, he may serve on a Subcommittee, but that is not always the case. Additionally, a Member may be on the main Committee, but no Subcommittee. Therefore if I exclude the "-", which is my ultimate goal, these committees will not appear in the query.

In terms of the query, I am using 4 tables, [HouseMember], [HouseCommitteeMember], [HouseSubcommittee], and [HouseMainCommittee]. I have established relationships, however as the table is currently set up, the [HouseMember] table relates to the [HouseCommitteeMember] table by the ID [HM_District], where as the [HouseCommitteeMember] table ID, [HCM_HSC_Abbreviation], relates to the [HouseSubcommittee] table's [HSC_Abbreviation] ID, and this table's [HSC_HMC_Abbrev] column then relates to [HouseMainCommittee] table's [HMC_Abbrev].

As the tables are set up, if a person serves on the Energy and Commerce Committee and the Energy and Commerce Subcommittee on Energy and Water Development, the table will show 2 entries ENEGAC and ENEGAC_ENGWAT. So in my query, that individual will have a "-" in the Subcommittee column relating to the Main Committee, and also have another entry where there will be both the SubCommittee name and the Committee Name. In this case, if I exclude the "-" when I incorporate this query into a report, the member will be associated with the Energy and Commerce Committee. Yet in other cases, the report will not relate the member to that committee.

So how would a junction table solve this?

 

[Moniker]

He's talking about normalizing your structure. See if this makes sense:

Table: [B]Congressperson[/B]

[U]PersonID (PK)   FName       LName[/U]
1               Cheating    Liar
2               Two-faced   Bastard
3               Old         Money

Table: [B]Committee_Type[/B]

[U]Committee_TypeID (PK)   Committee_Type[/U]
1                       Committee
2                       Sub-Committee

Table: [B]Committees[/B]

[U]Committee_ID (PK)   Committee_TypeID (PK)   PersonID (PK)   Committee_Name[/U]
1                   1                       1               Screw the Middle Class Committee
2                   1                       2               $150 million bus stop committee
2                   2                       2               $10 million for a toilet in the bus stop sub-scommittee
3                   1                       3               Ways and Means To Add To Slush Funds
3                   2                       1               Ways and Means To Hide Slush Fund

 

[The_Doc_Man]

Adding to what Moniker just said, you have another way to normalize this.

Person table:
Person ID (prime key)
Name
State
District
Party:Red/Blue/Grey/Depends on yesterday's headlines

Committee
CommID, prime key
Committee Name
MainCommittee (Yes/No, no for a SubCommittee)

CommMembers
Person ID
Committee ID
Person's rank in committee (Chair, ranking member, junior member, other way of describing the position). Could be encoded, could be short text.

OK, if you sort the CommMembers table by committee ID & rank you can generate a report of everyone on the committee and the rank they hold. By doing judicious (no, not judical - that's another committee) JOINS, you get to fill in the details. And that way you can build your report. Wheter you want to use + and - signs or some other symbology is your call.

 

[JerrodConaway]

Thanks again for your assistance. To better understand my problem, I have created the tables below. Unfortunately I cannot alter these tables, as I receive weekly updates from an external source, and cannot alter the basic structure. As you can see, these tables are linked, but the key problem, as AK_01 demonstrates, he is a member of the Main Transportation Committee, and 2 subcommittees, but only a member of the Resources Committee. So if I exclude the "-", from the subcommittee associated with the Resources Committee. then the Resources Committee entry will also be removed. Let me know if this clarifys my problem. Thanks again
HouseMember Table
HM_District.....HM_Last.....HM_First
AK_01............Young....... Don
AL_01............Bonner...... Jo
AL_02............Everett..... Terry

HouseCommitteeMember Table
HCM_District.....HCM_HSC_Abbreviation
AK_01.............TRANS
AK_01.............RESOURCES
AK_01.............TRANS_HIGHWAYS
AK_01.............TRANS_CG

HouseMainCommittee Table
HMC_Abbrev........HMC_Name
AG........................Committee on Agriculture
APPROP.................Committee on Appropriations
ARMED SERVICES....Committee on Armed Services
BUDGET................Committee on Budget
COMMERCE............Committee on Energy and Commerce
EDUCATION............Committee on Education and Labor
FS........................Committee on Financial Services

HouseSubcommittee Table
HSC_HMC_Abbrev............HSC_Name..............................HSC_Abbreviation
AG................................-...........................................AG
AG...............................Subcommittee on Conservation... AG_CON
AG...............................Subcommittee on Specialty ........AG_CROPS
AG...............................Subcommittee on Department..... AG_DEPT
AG...............................Subcommittee on General........... AG_GEN
AG...............................Subcommittee on Horticulture..... AG_HORTICULT
AG...............................Subcommittee on Livestock.........AG_LIVESTOCK
APPROP........................-............................................APPROP
APPROP........................Subcommittee on Agriculture.......APPROP_AG
APPROP........................Subcommittee on Defense...........APPROP_DEFENSE
APPROP........................Subcommittee on Energy.............APPROP_ENERGY