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

 

[Jeff Boyce]

Before you proceed, you may want to revisit the Access SQL. The "Last"
aggregation may not do what you think it does. If you are using this to try
to find the "most recent" date, use "Maximum". The "Last" command tells
Access to pick whatever it believes is the final entry in the table ...
ACCORDING TO ACCESS, not according to you.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sylvain Lafontaine]

Well, without telling us how you have migrated this Access, where do you
intent to put this query and to what version of SQL-Server you have upsized
this, it's hard to answer you how you should pass the variable GetUserId to
the SQL view and how to replace Last() function.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Lez]

Cheers Guys,

Thanks for your suggestions, I think I was making it mor complicated than it
needed, my solution has been to create a 'view' of the data in the SQL then
apply the other filters in the access FE in the query view window.

Amazing what sleep can give you sometimes

Many thanks
Les