• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Bi monthly query in Access

I have developed a "property management" database in Access 2000 and I need help developing one query.  Here is the situation:

I work for the U.S. Marshals Service in the Asset Forfeiture Program.  We are responsible for maintaining real estate seized by the DEA, FBI etc until a judge says we can sell it.  We currently have a private contractor that provides property management for us and my database will be used to keep tabs on the contractor.  I need help developing a query that will show all applicable records every two weeks from the date they were entered in the database.  For example, if I place an order for property management with the contractor I enter that order in my database.  Based on that order the contractor provides property management (grass cuts, security, etc) every two weeks until I cancel the order.  What Im trying to do is have my system prompt me every two weeks from the date I ordered property management so I can check to make sure the work is being done.  Thanks for any help you can provided.  Ive never used this system before so I am including my phone number in case I need to explain further.  Thanks again!

Rob Yergovich
202-307-5046
0
robyergovich
Asked:
robyergovich
  • 6
  • 3
  • 3
  • +1
1 Solution
 
joekendallCommented:
Can you give some detail about your table? Are you wanting to run the query have it show those that are due because it has been 2 weeks?

Joe
0
 
kkirt1Commented:
Without knowing the details of your database I will make some assumptions.

Lets say you have a table for properties and a table for services (i.e. one property may have more then one service.)  On the service table you would have a field that containes the start date and a field that indicates whether or not the service is still active.

The query you would run could simply look for all "services" that were active and where the difference between now and the service date divided by seven (weeks) was an even number.

To determine if the number is an even number, simply use the formula iif(([weeks]/2,0)*2=[weeks],"Even","Odd")

0
 
kkirt1Commented:
Let me know if something doesn't make sense or you need me to clarify my answer.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nibirkhanCommented:
I cannot give an automated procedure but I can tell you how to do it manually....

Create a date field "Date_Cancel" on your table where you have the "Date_Ordered", set the default value for "Date_Cancel" = Date_Ordered +15

Then create a report based on the "Date_Cancel" field, when you run the report on everyday to see if you have any order to cancel for today....

This is the manual way, I am sure there are automaed ways....you can consider my option as the worst case consideration...if you can't do it, lemme know or send me the file, I'll create one for you, you can email me at nibirkhan@hotmail.com

Good luck
0
 
robyergovichAuthor Commented:
Im still not quite sure how to use this system so I am responding to all of you here.

To Joe Kendall:
I have all the orders in one table with "Date Ordered", "Date Cancelled" etc.  I want all the orders that have not been canceled to show up on a query/report once every two weeks from the date ordered.  This will notify the users which properties should have had work done so they can verify the contractor did it.

To kkirt1:
I actually think your response is right on the money but I am a little confused about the formula.  What does dividing the "date ordered" by seven accomplish?  I think I can see where you are going with it but I still don't understand it exactly.

To nibirkhan:
Im not sure if you understood my problem exactly.  I think I understand your comment but I think that would fix it so the record would only show up once in the query.  Without having a date to store (since it will be different for each order) I need the query to show the record every two weeks from the date I ordered it.  For example an active order placed on 10/1/2003 would need to automatically show up on the query on 10/15/2003, 10/31/2003/, 11/1/2003, 11/15/2003, etc until it is canceled.
0
 
robyergovichAuthor Commented:
Sorry I messed up on those dates for my last comment.  An active order placed on 10/1/2003 would need to automatically show up on the query on 10/15/2003, 10/31/2003/, 11/15/2003, 11/30/2003, etc until it is canceled.

0
 
joekendallCommented:
SELECT *
FROM MyTable
WHERE DateCancelled Is Null
AND (Date()-DateOrdered) MOD 15 = 0

I am using every 15 days if you want every 2 weeks exactly you could substitute 14. The MOD will divide by 15 and see if there is a remainder. If there is, we haven't went 15 days out yet. If there is not, we can divide the amount by 15 evenly hence no remainder = 0.

Joe
0
 
joekendallCommented:
If no matter what date you start, you want to see them appear on the 15th and last day of the month then my solution will not work that way. We can make the change if you say so.

Joe
0
 
joekendallCommented:
SELECT *
FROM MyTable
WHERE DateCancelled Is Null
AND (Day(Date()) = 15) OR Day(Date()) = Day(DateAdd("m",1,Date())-Day(Date)))

That will do it if you want it to be on the 15th and last day of the month.

Joe
0
 
joekendallCommented:
That last post needs a change.

AND ((Day(Date()) = 15) OR (Day(Date()) = Day(DateAdd("m",1,Date())-Day(Date))))

Joe
0
 
robyergovichAuthor Commented:
Joe you are getting close.  I still don't think I have explained myself quite right yet. Your 14 day timefram is actually correct.  Let me add to my original order example:

An active order placed on 10/1/2003 would need to automatically show up on the query on 10/15/2003, 10/29/2003, 11/12/2003, 11/26/2003, etc until it is canceled.

An active order placed on 10/2/2003 would need to automatically shoe up on the query on 10/16/2003, 10/30/2003, 11/13/2003, 11/27/2003, etc until canceled.

An active order placed on 10/3/2003 would need to automatically show up on the query on 10/17/2003, 10/31/2003, 11/14/2003, 11/28/2003, etc until canceled.

Ive been reading your comment and maybe it accomplishes this and I just don't understand how it will make the appropriate records show up EVERY two weeks from the date ordered.  Thanks for all your help.  If I can get this part working I will be very happy.  
0
 
joekendallCommented:
This should do it. It will subtract the DateOrdered from Today's Date. If it is evenly divisible by 14 (14 days), then the MOD will return 0 which will include it on Today's Report. If it is not evenly divisible by 14, then it will not show up.

SELECT *
FROM MyTable
WHERE DateCancelled Is Null
AND (Date()-DateOrdered) MOD 14 = 0

Joe
0
 
kkirt1Commented:
Actually you would take (now()-ServiceDate)/7 = number of weeks since service date (weeks).  The second part of the formula is determining whether or not it is an even week.  

I think my suggestion would work: This forumula placed in the parameter of the query would only return records where even number of weeks have transpired

iif(((now()-ServiceDate)/7/2,0)*2=(now()-ServiceDate)/7,"Even","Odd")

I have not used the MOD function so it may work also.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now