D
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
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