Re: Is Access SQL FROM Clause different from MS SQL Server SQL?
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 10/20/04
- Next message: Tom Ellison: "Re: Ranking and restarting the rank on new product"
- Previous message: MN: "Change value for multiple columns?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 20 Oct 2004 14:26:32 -0500
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
On 20 Oct 2004 11:49:48 -0700, david_perkins1969@hotmail.com (David
Perkins) wrote:
>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
- Next message: Tom Ellison: "Re: Ranking and restarting the rank on new product"
- Previous message: MN: "Change value for multiple columns?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
