Date not within range query

[Guest]

I am working with a database that has a number of related tables. The query
I am building only concerns two however. Entities and Last Seen. I have
created a form that asks for the date range with txtDateTo and txtDateFrom
text boxes. I wish to execute a query that retrieves all of the entities
that do not have any Last Seen linked records with a date that falls in the
range. So I want only the Entities that where not seen during a date range.
The Entities table has a one to many relationship with the Last Seen table.
I have tried a number of combinations including

SELECT Entities.* FROM Entities INNER JOIN LastSeen ON
Entities.[EntitiyKey]=LastSeen[EntityKey] WHERE LastSeen.Date >
Forms!FindNoCoverage!ToDate AND LastSeen.Date < Forms!FindNoCoverage!FromDate

It does not return an error, but by doing some research in the tables, there
are date gaps that I used to test the query and it did not return the
records. This is my first query messing with JOINS and finding records that
have no linked records that fall inside a date range.

Any help will be appreciated.

Cory

 

[John Spencer]

Two query solution

Save following as QSeen
SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate)

Now use that and your entities table to do an unmatched query.
SELECT Entities.*
FROM Entities LEFT JOIN QSeen
On Entities.EntityKey = QSeen.EntityKey
WHERE QSeen.EntityKey is Null

Or all in one query IF field names or tablenames don't require brackets
SELECT Entities.*
FROM Entities LEFT JOIN
(SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate) as L
On Entities.EntityKey = L.EntityKey
WHERE L.EntityKey is Null

This may fail due to the use of Date as a field name. Date is a reserved
word in Access - it is a function that returns the current system date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I have noticed before that updating a field name in one place can update it
in another. Does this work fairly univerallsy? Would it change the
reference to that Field name in every place that it appears?

Cory

Two query solution

Save following as QSeen
SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate)

Now use that and your entities table to do an unmatched query.
SELECT Entities.*
FROM Entities LEFT JOIN QSeen
On Entities.EntityKey = QSeen.EntityKey
WHERE QSeen.EntityKey is Null

Or all in one query IF field names or tablenames don't require brackets
SELECT Entities.*
FROM Entities LEFT JOIN
(SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate) as L
On Entities.EntityKey = L.EntityKey
WHERE L.EntityKey is Null

This may fail due to the use of Date as a field name. Date is a reserved
word in Access - it is a function that returns the current system date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am working with a database that has a number of related tables. The
query
I am building only concerns two however. Entities and Last Seen. I have
created a form that asks for the date range with txtDateTo and txtDateFrom
text boxes. I wish to execute a query that retrieves all of the entities
that do not have any Last Seen linked records with a date that falls in
the
range. So I want only the Entities that where not seen during a date
range.
The Entities table has a one to many relationship with the Last Seen
table.
I have tried a number of combinations including

SELECT Entities.* FROM Entities INNER JOIN LastSeen ON
Entities.[EntitiyKey]=LastSeen[EntityKey] WHERE LastSeen.Date >
Forms!FindNoCoverage!ToDate AND LastSeen.Date <
Forms!FindNoCoverage!FromDate

It does not return an error, but by doing some research in the tables,
there
are date gaps that I used to test the query and it did not return the
records. This is my first query messing with JOINS and finding records
that
have no linked records that fall inside a date range.

Any help will be appreciated.

Cory

 

[John Spencer]

When Name Autocorrect works it is nice. The problem is that this feature
can cause problems and often fails to work. I always turn it off.

see Failures caused by Name AutoCorrect
http://allenbrowne.com/bug-03.html

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have noticed before that updating a field name in one place can update it
in another. Does this work fairly univerallsy? Would it change the
reference to that Field name in every place that it appears?

Cory

Two query solution

Save following as QSeen
SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate)

Now use that and your entities table to do an unmatched query.
SELECT Entities.*
FROM Entities LEFT JOIN QSeen
On Entities.EntityKey = QSeen.EntityKey
WHERE QSeen.EntityKey is Null

Or all in one query IF field names or tablenames don't require brackets
SELECT Entities.*
FROM Entities LEFT JOIN
(SELECT EntityKey
FROM LastSeen
WHERE LastSeen.Date > Forms!FindNoCoverage!ToDate
AND LastSeen.Date < Forms!FindNoCoverage!FromDate) as L
On Entities.EntityKey = L.EntityKey
WHERE L.EntityKey is Null

This may fail due to the use of Date as a field name. Date is a reserved
word in Access - it is a function that returns the current system date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am working with a database that has a number of related tables. The
query
I am building only concerns two however. Entities and Last Seen. I
have
created a form that asks for the date range with txtDateTo and
txtDateFrom
text boxes. I wish to execute a query that retrieves all of the
entities
that do not have any Last Seen linked records with a date that falls in
the
range. So I want only the Entities that where not seen during a date
range.
The Entities table has a one to many relationship with the Last Seen
table.
I have tried a number of combinations including

SELECT Entities.* FROM Entities INNER JOIN LastSeen ON
Entities.[EntitiyKey]=LastSeen[EntityKey] WHERE LastSeen.Date >
Forms!FindNoCoverage!ToDate AND LastSeen.Date <
Forms!FindNoCoverage!FromDate

It does not return an error, but by doing some research in the tables,
there
are date gaps that I used to test the query and it did not return the
records. This is my first query messing with JOINS and finding records
that
have no linked records that fall inside a date range.

Any help will be appreciated.

Cory