Need an Expert... Update with a DMAX

Ok peeps my head hurts and its seriously taxing my time and patience i think
its time for the A Team lol (A=Access)

ok heres my issue.

im creating a database that will accept all changes in movement from Staff
within the company. we have a HR system that will provide us with change
requests that hav been completed. importing this file is fine completing it
to table is fine. heres the tricky part

before i append my records in to the main records table i need to find the
newest current record in the main table and update it the date end with the
date started (-1) from the new records. so i created a max date query on the
main table and inner joined the personal numbers with the temptable personal
numbers and Voila finds the correct dates and records

now i must say that a few of the people in the database have three to four
active records and the same none active so its only the latest start dated
records i need.

so i tried to create the update (after reading so many google pages and Dmax
questions in here) i actually thought i had it

but the below SQL does not work how i need it to, the DMAX doesnt work and i
end up updating all the records in MAin Records table to the new End date and
this isnt what i need

i just need the records with the newest Start Date to have the Date Ended
cell updated with the new Temptable Start date and totally ignore the older
records.

UPDATE [Main Records] AS M INNER JOIN Temptable2 AS T ON M.[Personal Number]
= T.[Personal Number] SET M.[Date Ended] = T.[Date Started]-1
WHERE (((M.[Date Started])=(SELECT Max([m].[Date Started]) FROM [Main
Records])));

Can anyone help, as seriously struggling to keep from throwing laptop out of
fourth floor window ^^

Rivers

PERHAPS what you want is the following. I say "perhaps" because I get confused
while reading your explanation.

UPDATE [Main Records] AS M INNER JOIN Temptable2 AS T
ON M.[Personal Number] = T.[Personal Number]

SET M.[Date Ended] = T.[Date Started]-1

WHERE M.[Date Started] =
(SELECT Max([m2].[Date Started])
FROM [Main Records] As M2
WHERE M2.[Personal Number] = M.[Personal Number])

That should work except if TempTable2 has more than one record per Personal
Number. In that case you should get one of the dates from temptable for a
specific person, but not necessarily the earliest.

If Date Ended field is null for all the records that need to be updated and
not null for all the other records in Main Records you could probably use:

UPDATE [Main Records] AS M INNER JOIN Temptable2 AS T
ON M.[Personal Number] = T.[Personal Number]
SET M.[Date Ended] = T.[Date Started]-1
WHERE M.[Date Ended] is Null


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

Rivers wrote:
Ok peeps my head hurts and its seriously taxing my time and patience i think
its time for the A Team lol (A=Access)

ok heres my issue.

im creating a database that will accept all changes in movement from Staff
within the company. we have a HR system that will provide us with change
requests that hav been completed. importing this file is fine completing it
to table is fine. heres the tricky part

before i append my records in to the main records table i need to find the
newest current record in the main table and update it the date end with the
date started (-1) from the new records. so i created a max date query on the
main table and inner joined the personal numbers with the temptable personal
numbers and Voila finds the correct dates and records

now i must say that a few of the people in the database have three to four
active records and the same none active so its only the latest start dated
records i need.

so i tried to create the update (after reading so many google pages and Dmax
questions in here) i actually thought i had it

but the below SQL does not work how i need it to, the DMAX doesnt work and i
end up updating all the records in MAin Records table to the new End date and
this isnt what i need

i just need the records with the newest Start Date to have the Date Ended
cell updated with the new Temptable Start date and totally ignore the older
records.

UPDATE [Main Records] AS M INNER JOIN Temptable2 AS T ON M.[Personal Number]
= T.[Personal Number] SET M.[Date Ended] = T.[Date Started]-1
WHERE (((M.[Date Started])=(SELECT Max([m].[Date Started]) FROM [Main
Records])));

Can anyone help, as seriously struggling to keep from throwing laptop out of
fourth floor window ^^

Rivers