Date help

[jimhmason]

I have a database showing all customers who have had their tennis racquet restrung and the date listed when they had it restrung. I am trying to write a query which tells me those customers who have NOT had their racquet restrung within the past 1 year. The hope being each month I will genearte a list of customers who have not had their racquet restrung within the past year and I can contact them indicating as such.

[alansidman]

In the criteria for the date listed field of your query, type:

>date()+365

[jimhmason]

Not sure that is what I am looking for......My first record in my database is June 1, 2010 and it contiunes through the present. On July 1, 2011 I want to run a query that tells me all the people who have NOT had their racquet restrung in 1 year. Thus, it should look at ALL the people who had their racquet strung in June of 2010 and have NOT had it strung since. Some people do so I need to weed those out.

[alansidman]

Oops! My Bad. Got my hands working faster than my brain. Try this

>Date()-365
or
>Now()-365

Alan

[jimhmason]

I appreciate you helping me with this, but still not what I am looking for. This suggestion still just pulls all records.

Again, I have a database that list the dates of each person who gets their racquet strung. For example 15 people had their racquet strung in June of 2010. In June of 2011 I want to search the database (via query) and have it tell me which of those 15 people did NOT have their racquet strung again between June 2010 and June 2011. For example maybe one of those people had it strung again in September of 2010. I ONLY want it to list for me the people who DID NOT have their racquet strung within the past year. Thus, this one person who had it strung again within the past year would NOT show up on the list.

[alansidman]

I have tested this on a made up database that I created. It works and gives me all dates greater than one year old. Perhaps, if you posted your db with dummy data and we can see exactly what you are doing and offer a valid solution. Right now because we can't see your db, we are only guessing at solutions. If you cannot post your db, then at least show us your SQL Statement for the query.

Alan

[jimhmason]

Is there a way to send it as an attachment?

[alansidman]

When you reply, if you scroll down, you should see an opportunity to Manage Attachments. If you don't see it, it is because you don't have enough time in the forum. If that is the case, post it to box.net or some other similar site and then share the URL on this forum so that someone here can open it and review.

Alan

[jimhmason]

Here is a link to the sample database. I think I am doing a bad job explaining what I want from the query. Each record from the database contains a time when a customer came to our pro shop and had their racquet restrung. Some people come 2, 3 or 5 times a year. However, we are trying to identify those customers who have NOT had their racquet strung within a time period of 1 year. In the sample I have identified a person by the name Katie Engleking. She had her racquet restrung on 4/10/2010, then again on 7/10/2010 and then again on 5/8/2011. Thus she should not appear on the report because she HAS had her racquet restrung within the past year on 7/10/2010 and again on 5/8/2011. However, when I run the Query called "Query 1" that you provided in the example, the 4/10/2010 entry for Katie Engleking appears and it should not as she did have her racquet restrung within the past year. That is the little "bug" that I can NOT find a work around for. Thanks again so much!

Here is a link so you can download and look at the database.

http://www.box.net/shared/ph89zjve64

[alansidman]

Thanks for sharing it with me Jim and the last explanation. I created an Aggregate Query, with the max date as the aggregating item for each person. Then applied the same criteria. In this manner, we now get only the last stringing event to evaluate and if its been more than a year, then it should appear. See attached.

BTW, if you are not the only person using the db, you should seriously consider using Forms as your means to enter data and keep people out of your tables. Tables can get messed up very easily.

Alan