R
Rivers
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 ^^
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 ^^