Updating minimum and maximum dates in a series of dates

[Guest]

I have a table that contains a date field. This table contains a date field.
There are multiple records that contain all the same data with the exception
of this date field. I would like to be able to get Access to determine the
earliest and latest date for the records that contain all the same data with
the exception of the date. I can get the minimum and maximum dates to
populate on the correct records but if there is a month missing in the series
of dates, I would like for the minimum and maximum to be populated for each
series of dates.

For example, I have similar records for each month in the year 2007. I have
11 records, on for each month, January thru May and July thru December. I
want Access to update the first series of records with the beginning date of
January and the ending date of May. Then I want Access to update the minimum
date on the July thru December records with the July thru December series of
dates. Is this possible to do? Thanks.

 

[Guest]

If you have multiple rows containing identical values in all columns other
than the date then, unless all columns in the table are its primary key, the
table is badly de-normalized. You should decompose it so that you have one
row for each set of rows with the identical values all the non-date columns
and a primary key column, e.g. an autonumber column. The dates should go in
a related table with a foreign key column referencing the primary key of the
other table and a column for the date values. Lets assume this table is
called MyDates and has columns MyID and MyDate the rows for the start of each
sequence can be updated with:

UPDATE MyDates AS MD1
SET MyDate = DATESERIAL(YEAR(MyDate),MONTH(MyDate),1)
WHERE NOT EXISTS
(SELECT *
FROM MyDates AS MD2
WHERE MD2.MyID = MD1.MyID
AND DATEDIFF("m",MD2.MyDate,MD1.MyDate) = 1)
AND Mydate =
(SELECT MIN(MyDate)
FROM MyDates AS MD3
WHERE MD3.MyID = MD1.MyID
AND YEAR(MD3.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD3.MyDate) = MONTH(MD1.MyDate));

and the rows fro the end of each sequence with:

UPDATE MyDates AS MD1
SET MyDate = DATESERIAL(YEAR(MyDate),MONTH(MyDate)+1,0)
WHERE NOT EXISTS
(SELECT *
FROM MyDates AS MD2
WHERE MD2.MyID = MD1.MyID
AND DATEDIFF("m",MD1.MyDate,MD2.MyDate) = 1)
AND Mydate =
(SELECT MAX(MyDate)
FROM MyDates AS MD3
WHERE MD3.MyID = MD1.MyID
AND YEAR(MD3.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD3.MyDate) = MONTH(MD1.MyDate));

If you have a date in a single row for a month with no previous or following
month for that ID then that row's MyDate column would be updated to the
start or the end of the one-date depending on which of the above queries you
execute first.

Ken Sheridan
Stafford, England

 

[Guest]

As regards the following point in my first reply:

"If you have a date in a single row for a month with no previous or following
month for that ID then that row's MyDate column would be updated to the
start or the end of the one-date depending on which of the above queries you
execute first."

You could if you wished overcome this and leave any dates in this category
unchanged by amending the queries as follows:

For updating the start of each sequence:

UPDATE MyDates AS MD1 SET MyDate = DATESERIAL(YEAR(MyDate),MONTH(MyDate),1)
WHERE NOT EXISTS
(SELECT *
FROM MyDates AS MD2
WHERE MD2.MyID = MD1.MyID
AND DATEDIFF("m",MD2.MyDate,MD1.MyDate) = 1)
AND Mydate =
(SELECT MIN(MyDate)
FROM MyDates AS MD3
WHERE MD3.MyID = MD1.MyID
AND YEAR(MD3.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD3.MyDate) = MONTH(MD1.MyDate))
AND (SELECT COUNT(*)
FROM MyDates AS MD4
WHERE MD4.MyID = MD1.MyID
AND YEAR(MD4.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD4.MyDate) = MONTH(MD1.MyDate))>1;

For updating the end of each sequence:

UPDATE MyDates AS MD1 SET MyDate = DATESERIAL(YEAR(MyDate),MONTH(MyDate)+1,0)
WHERE NOT EXISTS
(SELECT *
FROM MyDates AS MD2
WHERE MD2.MyID = MD1.MyID
AND DATEDIFF("m",MD1.MyDate,MD2.MyDate) = 1)
AND Mydate =
(SELECT MAX(MyDate)
FROM MyDates AS MD3
WHERE MD3.MyID = MD1.MyID
AND YEAR(MD3.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD3.MyDate) = MONTH(MD1.MyDate))
AND (SELECT COUNT(*)
FROM MyDates AS MD4
WHERE MD4.MyID = MD1.MyID
AND YEAR(MD4.MyDate) = YEAR(MD1.MyDate)
AND MONTH(MD4.MyDate) = MONTH(MD1.MyDate))>1;

Ken Sheridan
Stafford, England