UPDATE ACCESS TABLE

[Andre7266]

I have 4 columns of interest in my table
userid...number
groupcode...text(10)
startdate...datetime
enddate....datetime

records are organized by userid by groupcode
example

125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:15 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:17 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:19 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:22 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:25 AM
125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:29 AM

Each group has the same startdate throughout with different enddate. What I
am trying to do is to change the next startdate/time to the previous record
enddate/time. Results would look as follows

125 AH0055A 1/22/2010 08:00 AM 1/22/2010 08:15 AM
125 AH0055A 1/22/2010 08:15 AM 1/22/2010 08:17 AM
125 AH0055A 1/22/2010 08:17 AM 1/22/2010 08:19 AM
125 AH0055A 1/22/2010 08:19 AM 1/22/2010 08:22 AM
125 AH0055A 1/22/2010 08:22 AM 1/22/2010 08:25 AM
125 AH0055A 1/22/2010 08:25 AM 1/22/2010 08:29 AM

I can do this manually for 10 or 20 records but I have 25,000 records to
update. Can anyone enlighten me as to how this can be done. Any help would be
greatly appreciated.

 

[Daryl S]

Andre7266 -

This looks like a job for Excel. First backup your Access database. Make
sure the data is sorted by userid, groupcode, startdate, enddate, then
copy/paste into Excel. Assuming the 25000 rows are in columns A-D and there
is a header in row 1, then your data will start in row 2.

In cell E2 put the following:
=IF(AND((A2=A1),(B2=B1),(C2=C1)),D1,C2)
Hit enter. If cell E2 looks like numbers instead of date/time values, then
just format the cell as date/time. Now click back on this cell and Copy.
Then select the rest of the cells below this one and Paste. If everything
looks good, then select the whole column E, do a Copy then a Paste Special |
Values. That will change the formula in column E to the real values. Then
you can copy column E and paste into column C, and then delete column E.
Your data can now be copy/pasted back into your Access table.

Let us know how it worked!

 

[Andre7266]

You say A2=A1, should that be A3=A2, A1 is my header. Should I change the
other references accordingly too?

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

You could try using an UPDATE query that used the DMax function. It might be
a bit slow

UPDATE YourTable
SET StartDate = Nz(DMax("EndDate","YourTable","USERID=" & UserID & " AND
GroupCode='" & GroupCode & "' AND EndDate<" & Format([EndDate],"\#yyyy-mm-dd
hh:nn:ss\#") ),[StartDate])


A faster method
Make a new empty table with the same structure as your existing table and then
populating it with an append query

INSERT INTO [TheNewTable] (UserID, GroupCode, EndDate, StartDate)
SELECT USERID, GroupCode, EndDate,
, NZ((SELECT Max(TempEndDate)
FROM Table As Temp
WHERE Temp.UserID = A.UserID
AND Temp.GroupCode = A.GroupCode
AND Temp.EndDate<A.EndDate),StartDate) as FixedStartDate
FROM Table as A

Now you can either replace your original table with the new table or use the
new table to update the existing table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Andre7266]

ooooops.....left out one left hand paren....once put in, seems to work just
fine.....Thanks very much for the help