Query for delinquent entries in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AnnG - 03 Nov 2005 20:11 GMT

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.

Reply to this Message

DevourU - 03 Nov 2005 20:48 GMT

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

-JS

> Please help with a query that will provide a list of delinquent entries.
> I
[quoted text clipped - 16 lines]
> Many
> thanks in advance.

Reply to this Message

AnnG - 03 Nov 2005 21:57 GMT

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.

> 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
[quoted text clipped - 22 lines]
> > Many
> > thanks in advance.

Reply to this Message

John Vinson - 04 Nov 2005 23:58 GMT

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

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]

Reply to this Message