sql query migration

  • Thread starter Thread starter Lez
  • Start date Start date
L

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
 
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
 
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)
 
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
 
Back
Top