Prev: find all 5 fields with Max date
Next: Converting text to a number in query
From: Peridot on 27 Jun 2008 17:01
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.
From: KARL DEWEY on 27 Jun 2008 19:06
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.
 | 
Pages: 1
Prev: find all 5 fields with Max date
Next: Converting text to a number in query