DateDiff >= 90 3

[pastorandy]

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));

 

[dhookom]

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).

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

 

[Andrzejek]

Something like:

[pre]
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))[red]
AND PK_Field NOT IN[/red][blue]
(Select PK_Field
from Table
Where DateDiff('d',[hymn_usage_date],Date()) < 90)[/blue]
[/pre]
Exclude the PK field of Hymns that were used in last 90 days.


---- Andy

There is a great need for a sarcasm font.

 

[pastorandy]

Thanks dhookom.

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

 

[dhookom]

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

 

[pastorandy]

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

 

[dhookom]

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

 

[pastorandy]

Many thanks. All working nicely now! )

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pastorandy]

Thanks Skip.
That's very good. )