Is Access SQL FROM Clause different from MS SQL Server SQL?

[David Perkins]

I am a beginning MS SQL Server 2K developer with about 6 mo of
experience. I have never messed with Querying Access databases.
Recently, I was approached by a member of my team who is trying to
tweak an Access query that was originally created automatically by
Access (and having no luck).

The first thing I noticed, was an odd syntax in the FROM statement.

Basically, the original (working) query looks like this:

SELECT

tbl1.IssueNo,
tbl1.AffectedProjectTeams,
tbl1.AffectedWorkGroups,
tbl1.AffectedRegions,
tbl1.Description,
tbl1.ActionPlan,
tbl1.FollowUpDate,
tbl1.Resolution,
tbl1.ResolutionDate,
tbl1.NeedByDate,
tbl1.FollowUpBy,
tbl1.Priority,
tbl1.Title,
tbl1.EscalationLevel,
tbl1.SubmittedBy,
tbl1.SubmittedDate,
tbl1.AssignedTo,
tbl1.Status,
tbl2.StatusOrder,
tbl3.PriorityOrder,
tbl4.AssignedTo,
tbl4.AssignedTo,
tbl5.Region


FROM tbl4, (tbl2
INNER JOIN (tbl3 INNER JOIN tbl1 ON
tbl3.Priority=tbl1.Priority) ON tbl2.Status=tbl1.Status)

INNER JOIN tbl5 ON tbl1.AffectedRegions like "*" & tbl5.Region &
"*"


WHERE (((tbl1.Status)="New" Or (tbl1.Status)="Open" Or
(tbl1.Status)="Deferred") And ((tbl1.EscalationLevel)="Team" Or
(tbl1.EscalationLevel)="Manager") And ((tbl1.AssignedTo) Like "*" &
tbl4.AssignedTo & "*") And ((tbl5.Region) Like "*" &
forms!Main!selectedRegion & "*")) And ((tbl5.RegionOrder)<>0);




My concern is with the FROM statement. I've only included the rest in
case anyone recognizes something that I don't.

The question I have is this:
These fields are being selected from a slightly complex join table,
but how does table 4 fit in to the mix?
It is listed with a comma after it, and with no joining conditions.
It may well be due to my lack of experience, but I have never seen SQL
syntax like this.

Can someone explain what this statement means?

I am currently on a client engagement and have no access to technical
documentation for MS Access.

Any help would be greatly appreciated.

Thanks,

Dave

 

[Tom Ellison]

Dear David:

The specific question about tbl4 in this query can be answered. The
syntax and functionality of this is the same as in SQL Server. It is
called a cross-product or CROSS JOIN. If you look at the help for
CROSS JOIN in SQL Server's Books Online, you will find a description
of this that applies to Access as well.

The syntax of representing a CROSS JOIN by just a comma may be less
familiar than specifying CROSS JOIN, but the comma syntax is accepted
by SQL Server as well, although I didn't see this fact in the portions
of Books Online I scanned before replying to you here.

The difference in Access I believe is that only the comma syntax is
used.

Other differences include the requirement in Access that the joins be
organized with parentheses. Access (Jet) has also added a large
number of parens in the FROM clause that may not have been in the
original designers intent.

A quick scan of the code indicates to me this query may work the same
in SQL Server as it does in Access if you change the wildcards from *
to %. It might be worth a try and may help satisfy your curiosity
about Access.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts