Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help with another missing function (Access 2K/SQL 2000)

    During my attempts to upsize our database to SQL and convert our .mdb to a project, I noted another missing function that we use a lot. I can't find any equivalent to First() or Last(). Any suggestions? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: help with another missing function (Access 2K/SQL 2000)

    Are you referring to the DFirst() and DLast() functions? You can get pretty much the same result with a query when you sort it either in ascending order or descending order and then specify the TOP 1 SQL clause. However, if you are using them on a form, it is a more complicated process in a project. Is this an experiment to see if you can make it work, or is that your marching orders?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with another missing function (Access 2K/SQL 2000)

    Wendell,

    We're trying to select the first instance of a customer in an aggregate query that's ideally supposed to make a special table/view. One instance is a cummulative count of outstanding stuff and the other can be left as a view. The two queries I have contain a lot of First(name of field) fields as we're trying not to duplicate records. Yes, we do have to go to .adp's. Do you need an example?
    Carpy Diem, it&#39;s .

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: help with another missing function (Access 2K/SQL 2000)

    Hmmm Peggy, so these are aggegrate queries and you aren't really doing a D function - I'll have to do a bit of digging to see what the TSQL syntax is. But if you simply want to eliminate duplicates, you can use the SELECT DISTINCTROW option in Access queries, so I'm still a little confused - I get that way easily BTW. And it seems to me that TSQL uses a different syntax for DISTINCT, but I don't remember it. I'll get back to you on both of those.

    My comment on ADPs was based on the fact that development of ADPs, particularly in 2000 is pretty cumbersome - they've made it a bit better in 2002, but we still think it takes about twice as long to build a front-end using an ADP as it does to do an MDB that links to SQL tables using ODBC. At one point, Microsoft people in high positions were saying that Jet would go away, and people would only use ADPs, but I only know of a couple of developers who have ever deployed an actual working ADP-based system, and I think current view is that MDB databases will be around for a long time to come. In your case it's likely you would be taking on city hall, so I understand, but our experience hasn't been all that favorable. If you want more details on where the difficulties arise, I'll dig in and put something together.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: help with another missing function (Access 2K/SQL 2000)

    OK, after digging through the T-SQL stuff, the best I can find is the Min and Max functions, but the syntax isn't anything like the way first and last work with GroupBy queries in Access. If your real objective is to not return multiple duplicate records, the DISTINCT keyword as a part of the SELECT clause should do the trick.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with another missing function (Access 2K/SQL 2000)

    Thanks, Wendell! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    We figured this out and darn it if I could find time to read all this stuff, this probably would have been solved faster. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •