sql query migration

[Lez]

Hi Guys,

sorry to cross post, not sure where this qiestions should be asked?

I had hoped I could simply copy the SQL code into a view and use I have this
query in my access fe, it list contact call backs for a specific user.

My questions are, as 'Last' is not supported in SQL what command would I
replace it with and as GetUserID is a variable stored in the access fe, how
can I pass this to the SQL view.

SELECT dbo_DS_investor_primary_contact.ds_xxxxxx_pcontactID,
dbo_DS_investor_primary_contact.ds_contact_first_name,
dbo_DS_investor_primary_contact.ds_contact_last_name,
Last(dbo_DS_investor_notes.ds_notes_next) AS LastOfds_notes_next,
dbo_DS_investor_primary_contact.ds_contact_userID
FROM dbo_DS_investor_primary_contact INNER JOIN dbo_DS_investor_notes ON
dbo_DS_investor_primary_contact.ds_xxxxxx_pcontactID =
dbo_DS_investor_notes.ds_notes_pcontactID
GROUP BY dbo_DS_investor_primary_contact.ds_xxxxxx_pcontactID,
dbo_DS_investor_primary_contact.ds_contact_first_name,
dbo_DS_investor_primary_contact.ds_contact_last_name,
dbo_DS_investor_primary_contact.ds_contact_userID
HAVING (((Last(dbo_DS_investor_notes.ds_notes_next)) Is Not Null) AND
((dbo_DS_investor_primary_contact.ds_contact_userID)=GetUsrUserID()))
ORDER BY Last(dbo_DS_investor_notes.ds_notes_next);

Thanks is advance

[Stefan Hoffmann]

hi Lez,

Post by Lez
My questions are, as 'Last' is not supported in SQL what command would I
replace it with and as GetUserID is a variable stored in the access fe,
how can I pass this to the SQL view.

There is no last entered record in SQL Server per se. You have two
possiblities based on the same principle:

1. You have to store the information about editing order using

a) an explicit DATETIME column using an update trigger to keep synchronized.

ALTER TABLE yourSchema.yourTable
ADD DateChanged DATETIME NOT NULL DEFAULT GETDATE()

CREATE TRIGGER yourSchema.trYourTableUPD
ON yourSchema.yourTable
FOR UPDATE
AS
SET NOCOUNT ON

DECLARE @DateChanged DATETIME = GETDATE() ;

UPDATE T
SET T.DateChanged = @DateChanged
FROM yourSchema.yourTable T
INNER JOIN Inserted I ON I.ID = T.ID ;

where ID at least a unique constraint or the primary key.

b) a ROWVERSION column. This column is database wide binary update
counter which is maintained by SQL Server (no trigger needed).

2.

a)
SELECT *
FROM yourTable O
WHERE O.yourColumn =
(
SELECT MAX(I.yourColumn)
FROM yourTable
)

b)
SELECT TOP 1 *
FROM yourTable
ORDER BY rowversionColumn DESC

Both solutions return the last edited record for further use. You may
encapsulat it in a CTE for better reading.

btw, Last() and DLast() should return the value of the field in the last
entered record. This is function which does _not_ always return what you
want.

mfG
--> stefan <--