Solved

Bi monthly query in Access

Posted on 2003-10-24
13
265 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

708 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

12 Experts available now in Live!

Get 1:1 Help Now