×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

DateDiff >= 90
3

DateDiff >= 90

DateDiff >= 90

(OP)
Hi
I have two tables [Hymn] and [Hymn_usage] and I want to select only the hymns that have a hymn_usage_date of 90 days or more, but also haven't been used within the last 90 days.

The problem is - while the query works by selecting only hymns greater than 90 days - a hymn can also appear more than once in hymn_usage and also appear in the results because it has been used in a record with a hymn_usage_date less than 90 days. Question is - how do i select a hymn that hasn't been used for 90 days or more but also hasn't been selected within the last 90 days as well? (this is possible because hymns can appear more than once in the hymn_usage table.)

Hymn: hymn_id, hymn_no, hymn_name
Hymn_usage: hymn_usage_id, hymn_number, hymn_usage_date

SELECT Hymn.hymn_no, Hymn.hymn_name, Hymn_usage.hymn_usage_date, DateDiff('d',[hymn_usage_date],Date()) AS [Days Since Used]
FROM Hymn INNER JOIN Hymn_usage ON Hymn.hymn_no = Hymn_usage.hymn_number
WHERE (((DateDiff('d',[hymn_usage_date],Date()))>=90));

RE: DateDiff >= 90

I think something like this might work since I believe you are only concerned with the most recent usage of any hymn Max(Hymn_usage_Date).

CODE --> sql

SELECT Hymn.hymn_no, Hymn.hymn_name, Max(Hymn_usage.hymn_usage_date) as MaxDate, DateDiff('d',Max(Hymn_usage.hymn_usage_date),Date()) AS [Days Since Used]
FROM Hymn INNER JOIN Hymn_usage ON Hymn.hymn_no = Hymn_usage.hymn_number
GROUP BY Hymn.hymn_no, Hymn.hymn_name
HAVING Date() - Max(Hymn_usage.hymn_usage_date) >=90 ; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DateDiff >= 90

Something like:

SELECT Hymn.hymn_no, 
       Hymn.hymn_name, 
       Hymn_usage.hymn_usage_date, 
       DateDiff('d',[hymn_usage_date],Date()) AS [Days Since Used]
 FROM Hymn INNER JOIN Hymn_usage 
       ON Hymn.hymn_no = Hymn_usage.hymn_number
 WHERE (((DateDiff('d',[hymn_usage_date],Date()))>=90))
   AND PK_Field NOT IN
  (Select PK_Field 
      from Table 
     Where DateDiff('d',[hymn_usage_date],Date()) < 90)
 
Exclude the PK field of Hymns that were used in last 90 days.


---- Andy

There is a great need for a sarcasm font.

RE: DateDiff >= 90

(OP)
Thanks dhookom.

I think this works, except that the hymn_names returned, appear to be in chinese characters. Weird!

RE: DateDiff >= 90

I suppose you don't speak Chinese. I would make a copy of your file and repair/compact.

What is the data type of the Hymn_Name? Is it short text?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DateDiff >= 90

(OP)
I read that it could be caused by having a memo field so I changed it to text 255 chars. That worked and the names are now displayed correctly.

One further thing, how can i then randomise the results?

I used the following in one query that worked.

Rnd(Int(Now()*Hymn.hymn_no)-Now()*hymn_no), Hymn_usage.hymn_usage_date;

How would that be incorporated in this new query?
Many thanks

RE: DateDiff >= 90

I think you can create a new query based on the one you just created and add the random column.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DateDiff >= 90

(OP)
Many thanks. All working nicely now! clown

RE: DateDiff >= 90

Pastor Randy, make sure this one’s in your database....
Oh Love That Will Not Let Me Go - George Matheson

https://youtu.be/ZiZ9xXoZ1Mk
1. O Love that wilt not let me go,

I rest my weary soul in thee;

I give thee back the life I owe,

That in thine ocean depths, its flow

May richer, fuller be.

2. O light that foll’west all my way,

I yield my flick’ring torch to thee;

My heart restores its borrowed ray,

That in thy sunshine’s blaze its day

May brighter, fairer be.

3. O Joy that seekest me through pain,

I cannot close my heart to thee;

I trace the rainbow through the rain,

And feel the promise is not vain,

That morn shall tearless be.

4. O Cross that liftest up my head,

I dare not ask to fly from thee;

I lay in dust life’s glory dead,

And from the ground there blossoms red
,
Life that shall endless be.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: DateDiff >= 90

(OP)
Thanks Skip.
That's very good. clown

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close