Same Field, Multiple Criteria

[Sara]

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Thanks in advance!

 

[KARL DEWEY]

Try this --
SELECT *, DateDiff("m", Date(), [YourDateField]) AS Age
FROM [YourTable]
WHERE DateDiff("m", Date(), [YourDateField]) >=5;

 

[Sara]

But I'm going to need the number for each age. 0 months through >13 months. I
want it to return the following:

0 Months - # Positions
1 Month - # Positions
2 Months - # Positions
etc, etc up to >13 Months

Try this --
SELECT *, DateDiff("m", Date(), [YourDateField]) AS Age
FROM [YourTable]
WHERE DateDiff("m", Date(), [YourDateField]) >=5;
--
KARL DEWEY
Build a little - Test a little

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Thanks in advance!

 

[Golfinray]

Try 13 columns (fields) and in the criteria of each field then you could have

.6, <.6, 6<>7, etc

But I'm going to need the number for each age. 0 months through >13 months. I
want it to return the following:

0 Months - # Positions
1 Month - # Positions
2 Months - # Positions
etc, etc up to >13 Months

Try this --
SELECT *, DateDiff("m", Date(), [YourDateField]) AS Age
FROM [YourTable]
WHERE DateDiff("m", Date(), [YourDateField]) >=5;
--
KARL DEWEY
Build a little - Test a little

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Thanks in advance!

 

[John Spencer]

Assuming two fields
Age and Position

TRANSFORM Count([Position]) as Count
SELECT "" as Blank
FROM YourQuery
GROUP BY ""
PIVOT Age

In the crosstab query grid
Field: Blank: ""
Total: Group by
Crosstab: Row Heading

Field: Age
Total: Group By
Crosstab: Column Heading

Field: Position
Total: Count
Crosstab: Value

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this --
SELECT Sum(IIF(DateDiff("m", Date(), [YourDateField])<1, 1,0)) AS [0
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=1, 1,0)) AS [1
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=2, 1,0)) AS [2
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=3, 1,0)) AS [3
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=4, 1,0)) AS [4
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=5, 1,0)) AS [5
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=6, 1,0)) AS [6
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=7, 1,0)) AS [7
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=8, 1,0)) AS [8
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=9, 1,0)) AS [9
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=10, 1,0)) AS [10
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=11, 1,0)) AS [11
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=12, 1,0)) AS [12
Months], Sum(IIF(DateDiff("m", Date(), [YourDateField])=13, 1,0)) AS [13
Months]
FROM [YourTable];

--
KARL DEWEY
Build a little - Test a little

But I'm going to need the number for each age. 0 months through >13 months. I
want it to return the following:

0 Months - # Positions
1 Month - # Positions
2 Months - # Positions
etc, etc up to >13 Months

Try this --
SELECT *, DateDiff("m", Date(), [YourDateField]) AS Age
FROM [YourTable]
WHERE DateDiff("m", Date(), [YourDateField]) >=5;
--
KARL DEWEY
Build a little - Test a little

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Thanks in advance!

 

[Sara]

Thank you! That worked perfectly!!

Assuming two fields
Age and Position

TRANSFORM Count([Position]) as Count
SELECT "" as Blank
FROM YourQuery
GROUP BY ""
PIVOT Age

In the crosstab query grid
Field: Blank: ""
Total: Group by
Crosstab: Row Heading

Field: Age
Total: Group By
Crosstab: Column Heading

Field: Position
Total: Count
Crosstab: Value

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Thanks in advance!

 

[Marshall Barton]

I might be going about doing this the wrong way, but this is what I'm trying
to do:

I have a query that takes the date a position was vacated and shows me the
age in months as compared to today's date. Now what I want to do is count the
number of positions with a certain age (i.e. there are X number of positions
that are 5 months old). I tried to do a Crosstab query but I don't have
enough fields. I also tried querying the same field and set criteria for each
age in multiple columns but when I do that it always returns "0". I'm sure
there's probably some way to do this, I just don't know how. Otherwise I
would have to set up 13 separate queries which just seems superfluous.

Try:

SELECT DateDiff("m", [date field], Date()) AS Age
Count(*) As CountOfAge
FROM [the table]
GROUP BY DateDiff("m", [date field], Date())

That age calculation might be too simplistic for your
purposes. If so, see if this meets your needs:
http://www.mvps.org/access/datetime/date0001.htm