Date query stuff

[anthony.carter]

Hi all,

I have a table with a list of people. Also included is the date that
they gained a certain qualification. This qualification needs to be
renewed every two years.

I need two pieces of information from this table.

The first is how many people renewed their qualification in the
previous calendar month. That is, if I run the query on the 10th of
March the query will return the number of people who renewed their
qualification during the month of February.

The second is a count of the number of people who still need to renew
their qualification before the end of the year.

I've managed to run queries before with 'have they haven't they?'
questions before but this 'date' criteria is making my head spin.
Needless to say I am a relative green newbie so any crumbs of help
will be gratefully received!

Thank you,

Tony

 

[Stefan Hoffmann]

hi Anthony,

The first is how many people renewed their qualification in the
previous calendar month. That is, if I run the query on the 10th of
March the query will return the number of people who renewed their
qualification during the month of February.

This should work:

WHERE Year([QualificationDate]) = Year(Date()) + (Month(Date()) = 1)
AND Month([QualificationDate]) = Month(Date()) - 1

The "+ (Month(Date()) = 1)" handles January with the previous year.

The second is a count of the number of people who still need to renew
their qualification before the end of the year.

This is an easier one:

WHERE [QualificationDate] < DateSerial(Year(Date()) - 2, 31, 12)

But take a look at the OH for the DateSerial() function, as I'm not
quite sure about the parameter order.


mfG
--> stefan <--

 

[Stefan Hoffmann]

WHERE Year([QualificationDate]) = Year(Date()) + (Month(Date()) = 1)
AND Month([QualificationDate]) = Month(Date()) - 1

Not quite correct:

AND Month([QualificationDate]) =
IIf(Month(Date()) - 1 = 0, 12, Month(Date()) - 1)


mfG
--> stefan <--

 

[anthony.carter]

Thanks Stefan. I'll give that a try.

Regards,

Tony

  WHERE Year([QualificationDate]) = Year(Date()) + (Month(Date()) = 1)
  AND Month([QualificationDate]) = Month(Date()) - 1

Not quite correct:

   AND Month([QualificationDate]) =
    IIf(Month(Date()) - 1 = 0, 12, Month(Date()) - 1)

mfG
--> stefan <--

 

[anthony.carter]

Hi,

Sorry to be a pain but I am uncertain as to what should be done with
your solution.

Does it need to be pasted into the criteria bit of the query or into
the SQL view (and if so where and how - i.e. one continuous line, with
or without spaces between the sections, or as laid out in the answer?)

I have tried a couple of methods but Access throws a fit about syntax.

Cheers,

Tony

 

[Stefan Hoffmann]

hi Anthony,

Does it need to be pasted into the criteria bit of the query or into
the SQL view

You have to paste it into the SQL view, e.g.

SELECT *
FROM [yourTable]
WHERE Year([yourDateField]) = Year(Date()) + (Month(Date()) = 1)
AND Month([yourDateField]) =
IIf(Month(Date()) - 1 = 0, 12, Month(Date()) - 1)

Replace [yourTable] and [yourDateField] with the appropriate values.


mfG
--> stefan <--

 

[John Spencer]

This query will probably give you what you want.

SELECT Abs(Sum(RenewalDate Between DateSerial(Year(Date()),
Month(Date())-1,1) and DateSerial(Year(Date()),Month(Date())-1,0)) as
MonthlyRenewal,
Abs(Sum(RenewalDate <= DateSerial(Year(Date())-1,12,31))) as NeedRenewal
FROM YourTable
WHERE RenewalDate < Date()

You can get the Counts one at a time by inserting.
Count renewals in previous month
--New Query
-- Select your table
-- Select the RenewalDate TWICE into the list of fields
-- Under the first Renewal date field enter the following as criteria
Between DateSerial(Year(Date()), Month(Date())-1, 1) and
DateSerial(Year(Date()), Month(Date()),0)
-- Select View Totals from the menu
-- Change Group by to WHERE under the first RenewalDate field
-- Change Group by to COUNT under the second RenewalDate Field

Count renewals that are outstanding
--New Query
-- Select your table
-- Select the RenewalDate TWICE into the list of fields
-- Under the first Renewal date field enter the following as criteria to get
all renewals that were last made on or before two years ago as of the end of
the year.
< DateSerial(Year(Date())-2, 12,31)
-- Select View Totals from the menu
-- Change Group by to WHERE under the first RenewalDate field
-- Change Group by to COUNT under the second RenewalDate Field

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..