ms-access syntax of a query

Question

I have a query that looks like:

select distinctrow company.* from company, contact, address, company 
  left join address on company.com_uid = address.com_uid, company 
  left join contact on company.com_uid = contact.com_uid

It is the base-query inside of an application which sets, upon user-input, dynamicaly the where-clause, e.g.:

where contact.function like 'C*'

or

where address.state = 'de'

this query shows all relevant companies. The App changes little to show the searched contacs:

select distinctrow contacts.* from company, contact, address, company 
  left join address on company.com_uid = address.com_uid, company 
  left join contact on company.com_uid = contact.com_uid

or the searched addresses:

select distinctrow address.* from company, contact, address, company 
  left join address on company.com_uid = address.com_uid, company 
  left join contact on company.com_uid = contact.com_uid

DISTINCTROW is mandatory because there are images and columns of datatype memo in all the tables. Beside the questions concerning performance, who know this sort of syntax and where does it come from?

Answer 1

DISTINCT versus DISTINCTROW

This article is designed for the person who is learning Access, as opposed to someone already conversant with SQL. But it's a fine explanation from either vantage.

http://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp

Answer 2

The Access Database Engine (Jet, ACE, whatever) does not implement the SQL-92 syntax; see Outer Join with WHERE Clause Returns Unexpected Records.

For even more detail, see this Joe Celko newsgroup thread:

Yes, ACCESS is dead wrong as usual. And they know about it. I got called in on this one as a consultant, to provide quotes from the SQL- 92 Standard. The Jet Engine gorup wanted to fix the parser, but some of the product groups in Microsoft have code that depends on these bugs. You see who won.

I can provide similar articles about the disaster that is DISTINCTROW if you like ;)

Answer 3

If I understand your question, you're asking why Access has a different style of SQL Syntax than SQL Server.

Access, and several other Microsoft products, use the Microsoft Jet Database Engine.

As you've noticed, there can be some frustrating differences between this flavor of SQL and what you're typically used to seeing in T-SQL.

In addition to the list of differences, this reference for Microsoft Jet in Access 2003 might be helpful for you.