access query trouble-HELP!

[schoenman]

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

 

[John Spencer]

You could try the following expression (note that I changed "= Between ..."
to "Between ..." and combined the OR into an IN clause.

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

Perhaps the following query will work for you - Note that I guessed at table
and field names since they don't seem to be consistent in your message.
They appear to be one thing in the IIF statement and something else in your
sample records. It doesn't return zero for those that weren't in the
government

SELECT MPName, Party, [Date], GOVT
FROM [Speeches March 27] as A INNER JOIN [Coalitions] as B
ON A.[Date] >= B.[Date Begins]AND
A.[Date]<= B.[Date Ends]
WHERE A.PartyAbbrev in (B.Party1,B.Party2,B.Party3,B.Party4)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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