Query for delinquent entries in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help with a query that will provide a list of delinquent entries. I
am using Access 2000.

I have an “INDICATOR†table with a field named “Indicator#†and a “DETAIL
RECORDS†table that also has a field with the “Indicator#†and a field named
“DateOfData†– along with many other fields of course.

I need a query that will provide a list of all the records from the
INDICATOR table that do not have entries in the “DETAIL RECORDS†table
greater than a date that will be entered in the “DateOfData†field of the
query.

If I have not made my problem clear, or if you need more info to help me,
please so inform.

I am very much a novice and will greatly appreciate any help offered. Many
thanks in advance.
 
Create a query with the indicator and detail records tables. Join indicator#
one to one. Add fields as needed. In the criteria for [DateOfData] place
#yourdate#.
Click to expand...

-JS
 
That is what sounded logical to me in the beginning, but the result I get is
a blank dataset. There are no dates in the "INDICATOR" table; therefore,
when I put a >date in the [DateOfData] field from the "DETAIL RECORDS"
table, it will only reference data from the "DETAIL RECORDS" table.

Maybe I'm using the wrong terminology. I need to 'extract' records from the
"INDICATOR" table that have no matching Inidicator Numbers in the "DETAIL
RECORDS" table for a date greater than the date I enter in the [DateOfData]
field from the "DETAIL RECORDS" table.

DevourU said:
Create a query with the indicator and detail records tables. Join indicator#
one to one. Add fields as needed. In the criteria for [DateOfData] place
#yourdate#.
Click to expand...

-JS

AnnG said:
Please help with a query that will provide a list of delinquent entries.
I
am using Access 2000.

I have an "INDICATOR" table with a field named "Indicator#" and a "DETAIL
RECORDS" table that also has a field with the "Indicator#" and a field
named
"DateOfData" - along with many other fields of course.

I need a query that will provide a list of all the records from the
INDICATOR table that do not have entries in the "DETAIL RECORDS" table
greater than a date that will be entered in the "DateOfData" field of the
query.

If I have not made my problem clear, or if you need more info to help me,
please so inform.

I am very much a novice and will greatly appreciate any help offered.
Many
thanks in advance.
Click to expand...
Click to expand...
 
Maybe I'm using the wrong terminology. I need to 'extract' records from the
"INDICATOR" table that have no matching Inidicator Numbers in the "DETAIL
RECORDS" table for a date greater than the date I enter in the [DateOfData]
field from the "DETAIL RECORDS" table.
Click to expand...

You need a Subquery then:

SELECT * FROM INDICATOR
WHERE [Indicator Number] NOT IN
(SELECT [Indicator Number] FROM [Detail Records]
WHERE [Detail Records].[DateOfData] > [Enter date:])


John W. Vinson[MVP]
 
Back
Top