Hi all,

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 query that adds a column to table 1,
which stores information about whether a speaker's party was a member
of the governing coalition on the date of the speech. If yes, it
should return the number associated with the coalition government (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 date range 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 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],"")

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). 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
.
.