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

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks, Wendell!

We figured this out and darn it if I could find time to read all this stuff, this probably would have been solved faster.

Reply | Quote

Thanks, Wendell!

We figured this out and darn it if I could find time to read all this stuff, this probably would have been solved faster.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote