robyergovich
asked on
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
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
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
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
Let me know if something doesn't make sense or you need me to clarify my answer.
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
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
ASKER
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.
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.
ASKER
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.
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
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
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
Joe
SELECT *
FROM MyTable
WHERE DateCancelled Is Null
AND (Day(Date()) = 15) OR Day(Date()) = Day(DateAdd("m",1,Date())-
That will do it if you want it to be on the 15th and last day of the month.
Joe
FROM MyTable
WHERE DateCancelled Is Null
AND (Day(Date()) = 15) OR Day(Date()) = Day(DateAdd("m",1,Date())-
That will do it if you want it to be on the 15th and last day of the month.
Joe
That last post needs a change.
AND ((Day(Date()) = 15) OR (Day(Date()) = Day(DateAdd("m",1,Date())-
Joe
AND ((Day(Date()) = 15) OR (Day(Date()) = Day(DateAdd("m",1,Date())-
Joe
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I have not used the MOD function so it may work also.
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
I have not used the MOD function so it may work also.
Joe