Dates and Queries (its hard to describe!)

[Lauren1989]

I'm hoping someone can help me out. I'm stuck.

Clients get reviewed on an annual, semi-annual, or quarterly basis. I am going to add two fields to my table: Date of Last Review (containing month/year) and Review Frequency (dropdown of 3, 6, 12 for quarterly, semi-annual, and annual). This is so I can run a query for each month and see what accounts need to be done. So far so good. Here's where I'm stuck. How do I then build a query so that:

Quarterlies show on query every 3 months
Semi-Annuals show on query every 6 months
Annuals show on query once a year



...all based on Date of Last Review. Therefore an account with a Date of Last Review of March 2010 and a frequency of 12 will show on query for March and an account with a Date of Last Review of March 2010 and a frequency of 3 will show on queries for March, June, September, and December.

PS- The year part in Date of Last Review can go if need be. The month is really what's important.

Thanks for any help.

[ketbdnetbp]

I'm hoping someone can help me out. I'm stuck.

Clients get reviewed on an annual, semi-annual, or quarterly basis. I am going to add two fields to my table: Date of Last Review (containing month/year) and Review Frequency (dropdown of 3, 6, 12 for quarterly, semi-annual, and annual). This is so I can run a query for each month and see what accounts need to be done. So far so good. Here's where I'm stuck. How do I then build a query so that:

Quarterlies show on query every 3 months
Semi-Annuals show on query every 6 months
Annuals show on query once a year

...all based on Date of Last Review. Therefore an account with a Date of Last Review of March 2010 and a frequency of 12 will show on query for March and an account with a Date of Last Review of March 2010 and a frequency of 3 will show on queries for March, June, September, and December.

PS- The year part in Date of Last Review can go if need be. The month is really what's important.

Thanks for any help.

First, it might be better to have the Date of Last Review as a short date. You'll see why from the attachment.

There are two queries, the first is based on the table. The second on the first query. The second is a Parameter Query, see [Which Month ?] and [Which Year ?] in the criteria areas. So, anytime you run the query, you would be prompted for the month and year. Enter a numeric value in both and the query will return those clients in need of a review for the month and year specified.

Hope this helps...

[ssanfu]

See if this (attached MDB) is what you want. There is a table and a query.

The field "LastReview" is type Date/Time. I would put the last review date in as the first of the month, although you could put the actual review date. The query works the same.

[Lauren1989]

Thank you both so much for your help! I really appreciate the attachments. That was very helpful. I can definitely do this now thanks to you.

[Lauren1989]

First, it might be better to have the Date of Last Review as a short date. You'll see why from the attachment.

There are two queries, the first is based on the table. The second on the first query. The second is a Parameter Query, see [Which Month ?] and [Which Year ?] in the criteria areas. So, anytime you run the query, you would be prompted for the month and year. Enter a numeric value in both and the query will return those clients in need of a review for the month and year specified.

Hope this helps...

Here is what I did, not sure why it's getting those error messages:

[ketbdnetbp]

See attached.

As a rule you should avoid using spaces in field names.

Secondly, when you added the AcctReview field with a yes/no data type, you referred to it using "Yes", it should be -1.

Give the attached a try and let me know how it works out for you.

All the best !!!

[Lauren1989]

Worked like a charm. I was able to integrate it perfectly into (a backup copy!) of our current database. Thank you again for your help.