Solved

Bi monthly query in Access

Posted on 2003-10-24
13
266 Views
Last Modified: 2012-08-13
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
Comment
Question by:robyergovich
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 11

Expert Comment

by:joekendall
ID: 9615569
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
 

Expert Comment

by:kkirt1
ID: 9615599
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
 

Expert Comment

by:kkirt1
ID: 9615610
Let me know if something doesn't make sense or you need me to clarify my answer.
0
 
LVL 2

Expert Comment

by:nibirkhan
ID: 9615701
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
 

Author Comment

by:robyergovich
ID: 9616172
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
 

Author Comment

by:robyergovich
ID: 9616196
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Expert Comment

by:joekendall
ID: 9616271
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9616277
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9616294
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9616788
That last post needs a change.

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

Joe
0
 

Author Comment

by:robyergovich
ID: 9617539
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
 
LVL 11

Accepted Solution

by:
joekendall earned 500 total points
ID: 9618406
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
 

Expert Comment

by:kkirt1
ID: 9619568
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now