Access DLast into SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Access DLast into SQL?

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-08-11 : 07:02:32

How do I convert this Access:

Notes: DLast("[FamiliesActionNote]","[Tbl_FamiliesProgress]","[FamiliesAction] like 'Withdrawn' AND [FamiliesID]=" & [Tbl_Families]![FamiliesID])

into SQL please? I've tried several variations and I can't get it to work :/

Thank you :)

Jim

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 07:54:00

The function DLast has no equivalent in SQL. According to the documentation,

quote:

The Microsoft Access DLast function returns the last value from an Access table (or domain).

but that makes no sense in SQL. SQL works with relations which are sets which have no concept of first or last value. Indeed, for large tables (that is, relations), it is common to see SQL return sets in seemingly random order. (What "last" means in Access is a mystery to me!)

Now, in this case there is a way around it, but you have to start with the question, "What is last?" To say that there is a "last" value implies some kind of ordering. Once you know that, you can write:


select top(1) myColumn
from myTable
order by myKey

Putting this together with your MS-Access statement, you might have something like this:


select top(1) [FamiliesActionNote]
from [Tbl_FamiliesProgress] p
join [Tbl_Families] f
  on p.[FamiliesID] = f.[FamiliesID]
where [FamiliesAction] ='Withdrawn' -- note that LIKE is the same as = when there are no wildcards
order by ...
[

you have to fill in the ORDER BY clause to ensure that the "last" value is at the TOP of the result set.

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-08-11 : 08:14:37

Hi GBritton,

Thanks for your detailed response. I was trying to use LAST in SQL but that didn't work.

I pasted what you suggested into Management Studio and it only brought up 1 record, not for all the records.

Also, when I added it to the remainder of the code I wrote, it didn't like it. The code I am using (with your suggestion added is) is:

SELECT TOP (100) PERCENT dbo.Tbll_Agents.AgencyName, dbo.Tbl_Families.FamiliesID, dbo.Tbl_Families.ApplicantCode, dbo.Tbl_Families.DateApproved, DATEDIFF(m,
dbo.Tbl_Families.DateApproved, dbo.Tbl_Families.Withdrawn) AS [Time Approved (Months)], dbo.Tbl_Families.AdoptionOrLTF, dbo.Tbl_Families.Withdrawn,
dbo.Tbl_Families.ReasonForWithdrawal AS [Withdrawal Reason], dbo.Tbl_Families.ApplicantRef AS Acquisition,
dbo.Tbll_SocialWorker.SWFirstname + N' ' + dbo.Tbll_SocialWorker.SWSurname AS SSW, dbo.Tbl_Families.AgeF, dbo.Tbl_Families.AgeM,
dbo.Tbl_Families.AgencyCode, dbo.Tbl_Families.FamilyCurrentPosition, (select top(1) [FamiliesActionNote]
from [Tbl_FamiliesProgress] p
join [Tbl_Families] f
on p.[FamiliesID] = f.[FamiliesID]
where [FamiliesAction] ='Withdrawn)
FROM dbo.Tbl_Families INNER JOIN
dbo.Tbl_FamilyDetails ON dbo.Tbl_Families.FamiliesID = dbo.Tbl_FamilyDetails.FamiliesID LEFT OUTER JOIN
dbo.Tbll_Agents ON dbo.Tbl_Families.AgencyCode = dbo.Tbll_Agents.AgencyCode LEFT OUTER JOIN
dbo.Tbll_SocialWorker ON dbo.Tbl_Families.SocialWorkerID = dbo.Tbll_SocialWorker.SocialWorkerID
WHERE (dbo.Tbl_Families.DateApproved IS NOT NULL) AND (NOT (dbo.Tbl_Families.FamilyCurrentPosition LIKE N'%re-assessment%')) AND
(dbo.Tbl_Families.Withdrawn IS NOT NULL)

ORDER BY dbo.Tbl_Families.ApplicantCode

What the Access Last was doing was looking for the most recent progress item called "Withdrawn" and pulling through the associated notes to that progress item, and linking it to the relevant FamilyID.

Thanks,

Jim :)

Jim

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 09:24:44

quote:

What the Access Last was doing was looking for the most recent progress item called "Withdrawn" and pulling through the associated notes to that progress item, and linking it to the relevant FamilyID.

Aha! So, there is an implied ordering by date!

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-08-11 : 09:31:54

yes, that's right! :)

Would you do it differently, knowing what I am trying to achieve? I'm at a loss how to do it!

Thanks

Jim

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 10:32:00

Can you build a test set for me to work on? That is:

1. CREATE TABLE Commands for the tables used in the query
2. INSERT INTO commands to populate the tables with test data
3. Results of running your query against the test tables
4. Desired results.

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-08-11 : 11:58:44

Hi, I have got this to work now (well, my colleague did!)

Thank you :)

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources