lookup value from a table based on two conditions

[schoenman]

I have been trying to figure this out for the last 14 hours and am
making almost no progress. Any help is greatly appreciated.

I have two tables: one is a large table of parliamentarians, dates of
their speeches in parliament, their party and other such info (many
thousands of rows). The other table is a list of 11 coalition
governments, dates in power and the parties that supported them (as
shown below).

What I would like to do is run a subquery (using an expression in a
field in design view) that adds a column to table 1,
which contains information about whether a speaker's party was a
member
of the governing coalition on the date of the speech. If yes, it
should lookup and return the number associated with the coalition
government from table 2 (a
number from 1-11), otherwise a "0".

So, I understand that I need to write an expression that checks if the
date of a speech falls in between the begin and end date of a
government AND
if the speaker's party was in the coalition on that date. I have tried
writing an expression using the SWITCH or IIF functions, to no avail.
Although I have tried many different things, the latest looks like
this:

Coalition:Iif(( [Speeches March 27]![Date] = Between [Coalitions]!
[Date Begins] And [Coalitions]![Date Ends] ) AND ([PartyAbbrev] =
[Coalitions]![Party1] OR [PartyAbbrev] = [Coalitions]![Party2] OR
[PartyAbbrev] = [Coalitions]![Party3] OR [PartyAbbrev]= [Coalitions]!
[Party4]) , [Coalitions]![Coalition Number],"0")

In particular, the problem seems to be that Access disregards the
second set of criteria (Party) and just works off the date ranges,
leaving me with numbers in every cell (since all the dates fall within
one of the ranges). If I run this expression in the criteria box, then
it does actually filter all the records that meet my condition, but I
want to add a value and see all the records. Not sure what I am doing
wrong. Any help or
pointers are MUCH appreciated. Thanks in advance,

Roger

Table 1
MPName DateofVote Party.....
John John 1/1/02 ABC
Mark Mark 1/8/03 DEF
..
..

Table 2
Govt StartDate EndDate Party1 Party2 Party3
1 1/1/95 1/1/98 ABC XYZ
2 2/1/98 1/1/00 DEF HGJ
..

 

[Guest]

Roger:

Your fundamental problem derives from the major design flaws in the tables
I'm afraid. You should not have separate columns for each party in a
coalition, but separate rows in a related table. There is a many-to-many
relationship between Parties and Coalitions, i.e. each party can be in one or
more coalitions (at different times), and each coalition is made up of more
than one party. The way a many-to-many relationship is modelled in a
relational database is by means of a third table, CoalitionParties say with
two foreign key columns referencing the primary keys of the two referenced
tables, in this case PartyID and CoalitionID for example. So if a coalition
5 included parties 2, 4 and 7 this would be represented by three rows in
CoaltionParties:

CoaltionID PartyID
5 2
5 4
5 7

The primary key of the table would be a composite one made up of both
columns as each pair of values in the table must be unique.

Your table of MPs should also be decomposed as at present it will contain a
large amount of redundancy, it being necessary to repeat the names, party etc
of each MP for each speech they make, and to judge by your sample data, every
time they vote. You should either have separate related tables for Votes and
Speeches, each with a foreign key MP_ID say, referencing the primary key of
an MPs table with one row per member, or a single related MemberActions table
again with a foreign key MP_ID say, referencing the primary key of an MPs
table, but also with a column ActionType with possible values "Speech',
'Vote' etc.

So, to return a list of MPs and dates of speeches made when the MP's party
was in power you'd use the MPs, Speeches, CoaltionParties tables, the latter
two being in a subquery correlated to the outer query on the party and dates,
like so:

SELECT MPName, SpeechDate
FROM MPs INNER JOIN Speeches
ON Speeches.MP_ID = MPs.MP_ID
WHERE EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= Speeches.SpeechDate
AND Coalition.[Date Ends] >= Speeches.SpeechDate)
ORDER BY MPName, SpeechDate;

or if using a MemberActions table:

SELECT MPName, ActionDate
FROM MPs INNER JOIN MemberActions
ON MemberActions .MP_ID = MPs.MP_ID
WHERE ActionType = "Speech"
AND EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= MemberActions .ActionDate
AND Coalition.[Date Ends] >= MemberActions .ActionDate)
ORDER BY MPName, ActionDate;

To return a corresponding list where the MP's party was not in power at the
time of the speech you'd simply change the EXISTS predicate to NOT EXISTS.

This does assume that no MP ever crosses the floor of the House and joins
another party of course. That would require another table to model the
many-to-many relationship between MPs and Parties!

One final point; avoid using Date (or any other keyword for that matter) as
a column name. It could cause confusion with the built in Date function.

Ken Sheridan
Stafford, England

I have been trying to figure this out for the last 14 hours and am
making almost no progress. Any help is greatly appreciated.

I have two tables: one is a large table of parliamentarians, dates of
their speeches in parliament, their party and other such info (many
thousands of rows). The other table is a list of 11 coalition
governments, dates in power and the parties that supported them (as
shown below).

What I would like to do is run a subquery (using an expression in a
field in design view) that adds a column to table 1,
which contains information about whether a speaker's party was a
member
of the governing coalition on the date of the speech. If yes, it
should lookup and return the number associated with the coalition
government from table 2 (a
number from 1-11), otherwise a "0".

So, I understand that I need to write an expression that checks if the
date of a speech falls in between the begin and end date of a
government AND
if the speaker's party was in the coalition on that date. I have tried
writing an expression using the SWITCH or IIF functions, to no avail.
Although I have tried many different things, the latest looks like
this:

Coalition:Iif(( [Speeches March 27]![Date] = Between [Coalitions]!
[Date Begins] And [Coalitions]![Date Ends] ) AND ([PartyAbbrev] =
[Coalitions]![Party1] OR [PartyAbbrev] = [Coalitions]![Party2] OR
[PartyAbbrev] = [Coalitions]![Party3] OR [PartyAbbrev]= [Coalitions]!
[Party4]) , [Coalitions]![Coalition Number],"0")

In particular, the problem seems to be that Access disregards the
second set of criteria (Party) and just works off the date ranges,
leaving me with numbers in every cell (since all the dates fall within
one of the ranges). If I run this expression in the criteria box, then
it does actually filter all the records that meet my condition, but I
want to add a value and see all the records. Not sure what I am doing
wrong. Any help or
pointers are MUCH appreciated. Thanks in advance,

Roger

Table 1
MPName DateofVote Party.....
John John 1/1/02 ABC
Mark Mark 1/8/03 DEF
..
..

Table 2
Govt StartDate EndDate Party1 Party2 Party3
1 1/1/95 1/1/98 ABC XYZ
2 2/1/98 1/1/00 DEF HGJ
..

 

[schoenman]

Roger:

Your fundamental problem derives from the major design flaws in the tables
I'm afraid. You should not have separate columns for each party in a
coalition, but separate rows in a related table. There is a many-to-many
relationship between Parties and Coalitions, i.e. each party can be in one or
more coalitions (at different times), and each coalition is made up of more
than one party. The way a many-to-many relationship is modelled in a
relational database is by means of a third table, CoalitionParties say with
two foreign key columns referencing the primary keys of the two referenced
tables, in this case PartyID and CoalitionID for example. So if a coalition
5 included parties 2, 4 and 7 this would be represented by three rows in
CoaltionParties:

CoaltionID PartyID
5 2
5 4
5 7

The primary key of the table would be a composite one made up of both
columns as each pair of values in the table must be unique.

Your table of MPs should also be decomposed as at present it will contain a
large amount of redundancy, it being necessary to repeat the names, party etc
of each MP for each speech they make, and to judge by your sample data, every
time they vote. You should either have separate related tables for Votes and
Speeches, each with a foreign key MP_ID say, referencing the primary key of
an MPs table with one row per member, or a single related MemberActions table
again with a foreign key MP_ID say, referencing the primary key of an MPs
table, but also with a column ActionType with possible values "Speech',
'Vote' etc.

So, to return a list of MPs and dates of speeches made when the MP's party
was in power you'd use the MPs, Speeches, CoaltionParties tables, the latter
two being in a subquery correlated to the outer query on the party and dates,
like so:

SELECT MPName, SpeechDate
FROM MPs INNER JOIN Speeches
ON Speeches.MP_ID = MPs.MP_ID
WHERE EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= Speeches.SpeechDate
AND Coalition.[Date Ends] >= Speeches.SpeechDate)
ORDER BY MPName, SpeechDate;

or if using a MemberActions table:

SELECT MPName, ActionDate
FROM MPs INNER JOIN MemberActions
ON MemberActions .MP_ID = MPs.MP_ID
WHERE ActionType = "Speech"
AND EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= MemberActions .ActionDate
AND Coalition.[Date Ends] >= MemberActions .ActionDate)
ORDER BY MPName, ActionDate;

To return a corresponding list where the MP's party was not in power at the
time of the speech you'd simply change the EXISTS predicate to NOT EXISTS.

This does assume that no MP ever crosses the floor of the House and joins
another party of course. That would require another table to model the
many-to-many relationship between MPs and Parties!

One final point; avoid using Date (or any other keyword for that matter) as
a column name. It could cause confusion with the built in Date function.

Ken Sheridan
Stafford, England

I have been trying to figure this out for the last 14 hours and am
making almost no progress. Any help is greatly appreciated.

I have two tables: one is a large table of parliamentarians, dates of
their speeches in parliament, their party and other such info (many
thousands of rows). The other table is a list of 11 coalition
governments, dates in power and the parties that supported them (as
shown below).

What I would like to do is run a subquery (using an expression in a
field in design view) that adds a column to table 1,
which contains information about whether a speaker's party was a
member
of the governing coalition on the date of the speech. If yes, it
should lookup and return the number associated with the coalition
government from table 2 (a
number from 1-11), otherwise a "0".

So, I understand that I need to write an expression that checks if the
date of a speech falls in between the begin and end date of a
government AND
if the speaker's party was in the coalition on that date. I have tried
writing an expression using the SWITCH or IIF functions, to no avail.
Although I have tried many different things, the latest looks like
this:

Coalition:Iif(( [Speeches March 27]![Date] = Between [Coalitions]!
[Date Begins] And [Coalitions]![Date Ends] ) AND ([PartyAbbrev] =
[Coalitions]![Party1] OR [PartyAbbrev] = [Coalitions]![Party2] OR
[PartyAbbrev] = [Coalitions]![Party3] OR [PartyAbbrev]= [Coalitions]!
[Party4]) , [Coalitions]![Coalition Number],"0")

In particular, the problem seems to be that Access disregards the
second set of criteria (Party) and just works off the date ranges,
leaving me with numbers in every cell (since all the dates fall within
one of the ranges). If I run this expression in the criteria box, then
it does actually filter all the records that meet my condition, but I
want to add a value and see all the records. Not sure what I am doing
wrong. Any help or
pointers are MUCH appreciated. Thanks in advance,

Table 1
MPName DateofVote Party.....
John John 1/1/02 ABC
Mark Mark 1/8/03 DEF
..
..

Table 2
Govt StartDate EndDate Party1 Party2 Party3
1 1/1/95 1/1/98 ABC XYZ
2 2/1/98 1/1/00 DEF HGJ
..

Ken,

Immense thanks for your reply. I understand how to store values for
the coalitions table now and that makes a lot of sense. I also do have
my other data stored in many tables- Table 1 is actually the result of
a join query, of tables that list separately speaker&party,
speaker&speech, long party name & abbreviation, etc. MPs do
unfortunately switch parties, parties change names and/or split into
new parties (the data come from Poland- a new democracy) so I have a
table for that as well.

That said, I'm still a bit confused about how to do what I am trying
to do: I'm not trying to return just a list of MPs from the ruling
coalition and their speeches (and that's what I understand your SELECT
statements will do). I want the table to always have all of the data
all of the time, but with a new column that contains the coalition
number if the speaker's party is part of the govt coalition, otherwise
a 0 (or even simpler, just calculates 1 if the speaker's party is part
of the ruling coalition (meets the date range and party criteria),
otherwise 0). In other words, I want to code each row with a value in
this new variable.

I think I am trying to use Access in a way that is somewhat unusual in
that I am not trying to select records based on a criteria, but add
information to all the records based on two conditions. At no point do
I want to reduce or filter records out of my dataset, since I want to
perform statistical analysis across all the records later. I could do
this easily in a statistics software package using IF...THEN
statements but am limited by the fact that it won't hold f more than
256 characters in a cell (and I have long speeches in one column,
which I would lose during the import). Ultimately, I will need to take
the Access table from this query and export to another program that
does work with text but won't do this kind of conditional coding.

I will try to work from your SELECT statements...if you are willing to
spare any more of your time, I very much appreciate your help. Many
thanks again.

Best,

Roger

 

[schoenman]

Roger:

Your fundamental problem derives from the major design flaws in the tables
I'm afraid. You should not have separate columns for each party in a
coalition, but separate rows in a related table. There is a many-to-many
relationship between Parties and Coalitions, i.e. each party can be in one or
more coalitions (at different times), and each coalition is made up of more
than one party. The way a many-to-many relationship is modelled in a
relational database is by means of a third table, CoalitionParties say with
two foreign key columns referencing the primary keys of the two referenced
tables, in this case PartyID and CoalitionID for example. So if a coalition
5 included parties 2, 4 and 7 this would be represented by three rows in
CoaltionParties:

CoaltionID PartyID
5 2
5 4
5 7

The primary key of the table would be a composite one made up of both
columns as each pair of values in the table must be unique.

Your table of MPs should also be decomposed as at present it will contain a
large amount of redundancy, it being necessary to repeat the names, party etc
of each MP for each speech they make, and to judge by your sample data, every
time they vote. You should either have separate related tables for Votes and
Speeches, each with a foreign key MP_ID say, referencing the primary key of
an MPs table with one row per member, or a single related MemberActions table
again with a foreign key MP_ID say, referencing the primary key of an MPs
table, but also with a column ActionType with possible values "Speech',
'Vote' etc.

So, to return a list of MPs and dates of speeches made when the MP's party
was in power you'd use the MPs, Speeches, CoaltionParties tables, the latter
two being in a subquery correlated to the outer query on the party and dates,
like so:

SELECT MPName, SpeechDate
FROM MPs INNER JOIN Speeches
ON Speeches.MP_ID = MPs.MP_ID
WHERE EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= Speeches.SpeechDate
AND Coalition.[Date Ends] >= Speeches.SpeechDate)
ORDER BY MPName, SpeechDate;

or if using a MemberActions table:

SELECT MPName, ActionDate
FROM MPs INNER JOIN MemberActions
ON MemberActions .MP_ID = MPs.MP_ID
WHERE ActionType = "Speech"
AND EXISTS
(SELECT *
FROM Coalitions INNER JOIN CoalitionParties
ON CoalitionParties.CoalitionID = Coalitions.CoalitionID
WHERE CoaltionParties.PartyID = MPs.PartyID
AND Coalition.[Date Begins] <= MemberActions .ActionDate
AND Coalition.[Date Ends] >= MemberActions .ActionDate)
ORDER BY MPName, ActionDate;

To return a corresponding list where the MP's party was not in power at the
time of the speech you'd simply change the EXISTS predicate to NOT EXISTS.

This does assume that no MP ever crosses the floor of the House and joins
another party of course. That would require another table to model the
many-to-many relationship between MPs and Parties!

One final point; avoid using Date (or any other keyword for that matter) as
a column name. It could cause confusion with the built in Date function.

Ken Sheridan
Stafford, England

I have been trying to figure this out for the last 14 hours and am
making almost no progress. Any help is greatly appreciated.
I have two tables: one is a large table of parliamentarians, dates of
their speeches in parliament, their party and other such info (many
thousands of rows). The other table is a list of 11 coalition
governments, dates in power and the parties that supported them (as
shown below).
What I would like to do is run a subquery (using an expression in a
field in design view) that adds a column to table 1,
which contains information about whether a speaker's party was a
member
of the governing coalition on the date of the speech. If yes, it
should lookup and return the number associated with the coalition
government from table 2 (a
number from 1-11), otherwise a "0".
So, I understand that I need to write an expression that checks if the
date of a speech falls in between the begin and end date of a
government AND
if the speaker's party was in the coalition on that date. I have tried
writing an expression using the SWITCH or IIF functions, to no avail.
Although I have tried many different things, the latest looks like
this:
Coalition:Iif(( [Speeches March 27]![Date] = Between [Coalitions]!
[Date Begins] And [Coalitions]![Date Ends] ) AND ([PartyAbbrev] =
[Coalitions]![Party1] OR [PartyAbbrev] = [Coalitions]![Party2] OR
[PartyAbbrev] = [Coalitions]![Party3] OR [PartyAbbrev]= [Coalitions]!
[Party4]) , [Coalitions]![Coalition Number],"0")
In particular, the problem seems to be that Access disregards the
second set of criteria (Party) and just works off the date ranges,
leaving me with numbers in every cell (since all the dates fall within
one of the ranges). If I run this expression in the criteria box, then
it does actually filter all the records that meet my condition, but I
want to add a value and see all the records. Not sure what I am doing
wrong. Any help or
pointers are MUCH appreciated. Thanks in advance,
Roger
Table 1
MPName DateofVote Party.....
John John 1/1/02 ABC
Mark Mark 1/8/03 DEF
..
..
Table 2
Govt StartDate EndDate Party1 Party2 Party3
1 1/1/95 1/1/98 ABC XYZ
2 2/1/98 1/1/00 DEF HGJ
..

Ken,

Immense thanks for your reply. I understand how to store values for
the coalitions table now and that makes a lot of sense. I also do have
my other data stored in many tables- Table 1 is actually the result of
a join query, of tables that list separately speaker&party,
speaker&speech, long party name & abbreviation, etc. MPs do
unfortunately switch parties, parties change names and/or split into
new parties (the data come from Poland- a new democracy) so I have a
table for that as well.

That said, I'm still a bit confused about how to do what I am trying
to do: I'm not trying to return just a list of MPs from the ruling
coalition and their speeches (and that's what I understand your SELECT
statements will do). I want the table to always have all of the data
all of the time, but with a new column that contains the coalition
number if the speaker's party is part of the govt coalition, otherwise
a 0 (or even simpler, just calculates 1 if the speaker's party is part
of the ruling coalition (meets the date range and party criteria),
otherwise 0). In other words, I want to code each row with a value in
this new variable.

I think I am trying to use Access in a way that is somewhat unusual in
that I am not trying to select records based on a criteria, but add
information to all the records based on two conditions. At no point do
I want to reduce or filter records out of my dataset, since I want to
perform statistical analysis across all the records later. I could do
this easily in a statistics software package using IF...THEN
statements but am limited by the fact that it won't hold f more than
256 characters in a cell (and I have long speeches in one column,
which I would lose during the import). Ultimately, I will need to take
the Access table from this query and export to another program that
does work with text but won't do this kind of conditional coding.

I will try to work from your SELECT statements...if you are willing to
spare any more of your time, I very much appreciate your help. Many
thanks again.

Best,

Roger

Actually, I think I got it with your help. Many thanks.
Roger