pastorandy
IS-IT--Management
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));
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));