Comparing data with the previous record

[Shaft128]

I have a table that is simplified as thus.

Name Date
Jack 01/01/01
Jack 01/02/01
Jack 01/03/01
Jack 01/05/01
Jack 01/06/01
Jack 01/07/01

As you will notice, the date 01/04/01 is missing. I need to find a way of detecting that fact. I have already worked out the theory behind doing it, but need to know how to compare the date for 1 record against the date for the previous record.

Any Ideas? Thanks.

 

[JerryDennison]

There are a couple of ways to do this. The most efficient method is to use subqueries. The following syntax can be used:

SELECT DateAdd("d",-1,[txtDate]) AS MissingDate
FROM YourTableName
WHERE (((DateAdd("d",-1,[txtDate]))>(SELECT Min(YourTableName.txtDate) FROM YourTableName)) AND (((SELECT txtDate FROM YourTableName as Alias WHERE Alias.txtDate = DateAdd("d", -1, YourTableName.txtDate))) Is Null));

NOTE: If you need to apply criteria or grouping you must also apply it to the subqueries. There are two subqueries being utilized. One in the criteria of the missing date field (to prevent the first record from being falsely reported as missing) and the other to find the missing values.

 

[Shaft128]

This works fine when there is only one day missing, but when I have more than one sequential day missing, it only displays the last of the missing days. I have reversed the query to find the first of the missing days, but this creates problems. It's difficult to explain, but if it finds a missing start date, it puts a value in the missing end date as well, which isn't a date that is missing.

My query currently looks like this.

SELECT datetesttable.ref, DateAdd("d",+1,[datetest]) AS [Missing Start Date], DateAdd("d",-1,[datetest]) AS [Missing End Date]
FROM datetesttable
WHERE (((DateAdd("d",+1,[datetest]))
<(SELECT max(datetesttable.datetest) FROM datetesttable)) AND
(((SELECT datetest FROM datetesttable as Alias WHERE Alias.datetest = DateAdd(&quot;d&quot;, +1, datetesttable.datetest)))
Is Null)) OR
(((DateAdd(&quot;d&quot;,-1,[datetest]))>(SELECT Min(datetesttable.datetest) FROM datetesttable)) AND
(((SELECT datetest FROM datetesttable as Alias WHERE Alias.datetest = DateAdd(&quot;d&quot;, -1, datetesttable.datetest)))
Is Null));

Is there another way of doing this, or a way around this problem? Cheers.

 

[Shaft128]

Also, as there is more than one name in the list, and each name has it's own range of dates, it is giving me the first date of a range if any other name has an earlier date.

 

[tomschafer]

Try this...

SELECT
a.Name,
a.Date+1 AS Gap
FROM
Table1 AS a,
Table1 AS b,
(SELECT b.Date FROM Table1 AS a, Table1 AS b WHERE b.Date-a.Date=1) AS q
WHERE
b.Date = q.Date and
a.Date+1<DMax(&quot;Date&quot;,&quot;Table1&quot And
a.Date+1 Not In (q.Date) AND
a.Date-b.Date=1

 

[Shaft128]

Sorry Pezamystik, but I can't get your example to work at all. It fails to do the (SELECT b.Date...) part and doesn't go any further.

I'm gonna re-explain what I need to do. Here is a table called DATELIST


name date
FRED 01/01/01
FRED 01/02/01
FRED 01/05/01
FRED 01/07/01
JACK 02/13/01
JACK 03/14/01
JACK 03/15/01
JACK 03/17/01

I need to create a list of the missing dates for each name, so I end up with a table that looks like this.

Name Missing Start Missing End
FRED 01/03/01 01/04/01
FRED 01/06/01 01/06/01
JACK 02/14/01 03/13/01
JACK 03/16/01 03/16/01

In Excel, this would be easy as I would just reference the value in the previous row, and use some If statements, but I am using an MS-Access Database, and there are too many records to output to Excel, plus I need to do this regularly.

Cheers.

 

[tomschafer]

Try this...

Save This as Query1 or some other name, just make sure to change it in the main query...

SELECT b.Date FROM Table1 AS a, Table1 AS b WHERE b.Date-a.Date=1

This is the new query...

SELECT
a.Name,
a.Date+1 AS Gap
FROM
Table1 AS a,
Table1 AS b,
Query1 AS q
WHERE
b.Date = q.Date and
a.Date+1<DMax(&quot;Date&quot;,&quot;Table1&quot And
a.Date+1 Not In (q.Date) AND
a.Date-b.Date=1

 

[Shaft128]

Sorry Pezamystik, but although that no longer comes up with errors, it doesn't tell me what I need to know, and it ignores a date if it is on it's own, i.e. the date both before and after is missing.

You have helped me agreat deal, as I have now almost worked out a way of doing this. For my test table, I have added a field in which I have put the number of the record, so that it looks like this.

Number Name Date

1 FRED 01/01/01
2 FRED 01/02/01
3 FRED 01/05/01
4 FRED 01/07/01
5 JACK 02/13/01
6 JACK 03/14/01
7 JACK 03/15/01
8 JACK 03/17/01

and have created 3 tables (I will try to merge them into 1 later)
[Pezamystic Test]
SELECT a.name, a.date, a.number
FROM datetesttable AS a;

[Pezamystic Test B]
SELECT b.name, b.date, b.number+1 AS added
FROM datetesttable AS b;

[Pezamystic Test C]
SELECT [pezamystik test b].name, [pezamystik test b].date+1 AS MissingFrom, [pezamystik test].date-1 AS MissingTill
FROM [pezamystik test]
INNER JOIN [pezamystik test b]
ON ([pezamystik test].name = [pezamystik test b].name)
AND ([pezamystik test].number = [pezamystik test b].added)
WHERE [pezamystik test b].date+1 <> [pezamystik test].date;

This now gives me the answer that I want from my test database. The problem I have now is that my actual database is too big to add the record number to each record, and I will have to do this regularly. Is there anyway I can get a query to put in a field the number of the record that it actually is?

 

[tomschafer]

Check out the FAQ page to see how to add an incremental value to a query.

Good luck

 

[Shaft128]

I couldn't get that incremental value function to work. Firstly, I couldn't get it to start at 0 for each query (I needed to run 2 queries, and the numbers had to be the same for each), also, everytime I refreshed the screen, the numbers incremented again.

I got around this problem by appending the Name and Date fields from each record, in the correct order (Sort by Name then Sort By Date) to a new table, and having an autonumber field in this table. This meant that each row was automatically numbered in the correct order, so I could run my query on that.

Also, I realised I no longer required the first query as that just told me what was in the table.