help me wrap my head around this

[Raphael Crawford-Marks]

I have a table that looks something like this:

ID |Type |Date1 |Date2 |
1 |A |7/1/2002 |9/1/2002 |
1 |B |9/1/2002 |10/17/2002 |
1 |C |12/1/2002 |3/1/2003 |
2 |B |1/1/2003 |2/1/2003 |
3 |A |8/1/2002 |3/1/2003 |
3 |C |4/1/2003 |5/1/2003 |

I need to run a query that will give me this:

ID |DaysOfA |DaysOfB |DaysOfC |
1 |62 |47 |90 |
2 |0 |31 |0 |
3 |212 |0 |30 |

As you can see, the query groups by ID, and counts the
number of days between Date1 and Date2 for each type. I
know I could do this by writing a query that counts the
days for each type individually (grouping by ID) and
joining the queries together. But I feel like there
should be a simpler way.

 

[Phobos]

A crosstab query will do this (without the zeros):

TRANSFORM Sum([Date2]-[Date1]) AS Expr1
SELECT tblDateCounter.ID
FROM tblDateCounter
GROUP BY tblDateCounter.ID
ORDER BY tblDateCounter.ID, tblDateCounter.Type
PIVOT tblDateCounter.Type;


P

 

[Jeff Boyce]

Raphael

Have you considered a cross-tab query?

Good luck

Jeff Boyce
<Access MVP>