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
robyergovichAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.