If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
make query to return a subset only if not in another subset
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. |
Thread Tools | |
Display Modes | |
|
|