make query to return a subset only if not in another subset

I have a table in Access 2003 that list all my accounts, what day I call
them, and what week I call them. Some accounts are called every week (weeks
1, 2, 3, and 4). If this is so, the table has separate entries for each week:

Account # Name Day Week
3400 Chez Andre M 1
3400 Chez Andre M 2
3400 Chez Andre M 3
3400 Chez Andre M 4

Some are called every other week (weeks 1 and 3, or weeks 2 and 4).

Account # Name Day Week
4400 Chuckles M 1
4400 Chuckles M 3


Lastly, some accounts are called once every 4 weeks (only week 1, ...).

Account # Name Day Week
5400 Charly's M 1


I am trying to write a query that will tell me which accounts are called
weeks 1 and 3, but not weeks 2 and 4, or which accounts are called only in
week 1. There has to be a way to tell Access to exclude the account if it is
in week 1 and weeks 2, 3, and/or 4 also, but I don't know it. I'd be very
grateful for any help.

First use a crosstab then nested IIFs ---
Peridot_Crosstab ---
TRANSFORM Sum(1) AS X
SELECT Peridot.[Account #]
FROM Peridot
GROUP BY Peridot.[Account #]
PIVOT Peridot.Week;

SELECT Peridot_Crosstab.[Account #], IIf([1]=1 And [3]=1 And [2] Is Null And
[4] Is Null,"1 and 3",IIf([1]=1 And [2] Is Null And [2] Is Null And [4] Is
Null,"1 only",IIf([1]=1 And [2]=1 And [2]=1 And [4]=1,"1 thru 4","Error")))
AS Expr1
FROM Peridot_Crosstab;

--
KARL DEWEY
Build a little - Test a little


"Peridot" wrote:

I have a table in Access 2003 that list all my accounts, what day I call
them, and what week I call them. Some accounts are called every week (weeks
1, 2, 3, and 4). If this is so, the table has separate entries for each week:

Account # Name Day Week
3400 Chez Andre M 1
3400 Chez Andre M 2
3400 Chez Andre M 3
3400 Chez Andre M 4

Some are called every other week (weeks 1 and 3, or weeks 2 and 4).

Account # Name Day Week
4400 Chuckles M 1
4400 Chuckles M 3


Lastly, some accounts are called once every 4 weeks (only week 1, ...).

Account # Name Day Week
5400 Charly's M 1


I am trying to write a query that will tell me which accounts are called
weeks 1 and 3, but not weeks 2 and 4, or which accounts are called only in
week 1. There has to be a way to tell Access to exclude the account if it is
in week 1 and weeks 2, 3, and/or 4 also, but I don't know it. I'd be very
grateful for any help.