C
Chris
Hi all,
I have 2 queries that do similar things. I've posted
the SQL for both below (as clearly as I could format
them). The first query is the one I was using, but I found
it to be slow and I've had to make a lot of calculations
on the form which slows it down quite a bit. So I picked
up a book and started reading and this led to QUERY2
below. The second query gives me more information but I
can't edit it when it's running. At first I thought that
the problem was that I am using multiple tables in the
second query but I realised that the first also does this.
I know that I can't edit the calculated fields, but I
would like to be able to edit the basic unchanged fields
from the original table. Can anyone tell me why 1 is
updatable and the other is not. Thanks for any help,
Chris.
QUERY #1
SELECT
tblPlanningApps.ProjectID,
tblPlanningApps.DateAdPublication,
tblPlanningApps.DateLodged,
tblPlanningApps.PlanningAppID,
tblPlanningApps.PlanningDesc,
tblPlanningApps.PlanningRef,
tblPlanningApps.NewspaperID,
tblPlanningApps.Applicant,
tblPlanningApps.AdWording,
tblPlanningApps.DateDecision,
tblPlanningApps.PlanningStatus,
tblPlanningApps.PermSought,
tblPlanningApps.DateSiteNotice,
tblPlanningApps.FeeAmount,
tblPlanningApps.Notes,
tblPlanningApps.NextDueItem,
tblPlanningApps.NextDueDate,
tblPlanningApps.CurrentStatus,
tblPlanningApps.PreparedBy,
tblPlanningHistory.State,
tblPlanningHistory.DateEntered,
tblPlanningHistory.Notes,
tblPlanningHistory.EmpID,
tblPlanningHistory.PlanningHistoryID,
tblPlanningApps.bActive
FROM tblPlanningApps RIGHT JOIN tblPlanningHistory ON
tblPlanningApps.PlanningAppID =
tblPlanningHistory.PlanningAppID
WHERE (((tblPlanningHistory.PlanningHistoryID)=
[CurrentStatus]))
ORDER BY tblPlanningApps.ProjectID,
tblPlanningApps.DateAdPublication,
tblPlanningApps.DateLodged;
//END OF QUERY #1
QUERY #2
SELECT
PA.PlanningAppID,
"P" & Format(PA.PlanningAppID, "00000") AS INTERNALREF,
PH.PlanningHistoryID,
PA.ProjectID,
(SELECT Format(PR.ProjectNum,"0000") & " - " & PR.Client
& " @ " & PR.Loc_Street & ", " & PR.Loc_Town & ", " &
(SELECT County FROM tblCountys AS C WHERE
PR.CountyID=C.CountyID) FROM tblProjects AS PR WHERE
PA.ProjectID = PR.ProjectID) AS PDETAIL,
PlanningDesc,
PlanningRef,
NewspaperID,
Applicant,
AdWording,
DateAdPublication,
DateLodged,
PlanningStatus,
PermSought,
DateSiteNotice,
PA.Notes,
PA.NextDueItem,
PA.NextDueDate,
PA.CurrentStatus,
PA.PreparedBy,
(SELECT Initials FROM tblEmployees AS E WHERE
E.EmployeeID=PA.PreparedBy) AS PrepInitials,
bActive,
PH.PlanningAppID,
PH.State,
PS.EndState AS bEndState,
PS.bPositiveOutcom AS bPosOutcome,
PS.bOurResponsibility,
PH.DateEntered,
PH.Notes AS StateComments
FROM tblPlanningApps AS PA, tblPlanningHistory AS PH,
tblPlanningStates AS PS
WHERE (PA.PlanningAppID = PH.PlanningAppID) AND
(PS.PlanningStateID = PH.State);
//END OF QUERY #2
I have 2 queries that do similar things. I've posted
the SQL for both below (as clearly as I could format
them). The first query is the one I was using, but I found
it to be slow and I've had to make a lot of calculations
on the form which slows it down quite a bit. So I picked
up a book and started reading and this led to QUERY2
below. The second query gives me more information but I
can't edit it when it's running. At first I thought that
the problem was that I am using multiple tables in the
second query but I realised that the first also does this.
I know that I can't edit the calculated fields, but I
would like to be able to edit the basic unchanged fields
from the original table. Can anyone tell me why 1 is
updatable and the other is not. Thanks for any help,
Chris.
QUERY #1
SELECT
tblPlanningApps.ProjectID,
tblPlanningApps.DateAdPublication,
tblPlanningApps.DateLodged,
tblPlanningApps.PlanningAppID,
tblPlanningApps.PlanningDesc,
tblPlanningApps.PlanningRef,
tblPlanningApps.NewspaperID,
tblPlanningApps.Applicant,
tblPlanningApps.AdWording,
tblPlanningApps.DateDecision,
tblPlanningApps.PlanningStatus,
tblPlanningApps.PermSought,
tblPlanningApps.DateSiteNotice,
tblPlanningApps.FeeAmount,
tblPlanningApps.Notes,
tblPlanningApps.NextDueItem,
tblPlanningApps.NextDueDate,
tblPlanningApps.CurrentStatus,
tblPlanningApps.PreparedBy,
tblPlanningHistory.State,
tblPlanningHistory.DateEntered,
tblPlanningHistory.Notes,
tblPlanningHistory.EmpID,
tblPlanningHistory.PlanningHistoryID,
tblPlanningApps.bActive
FROM tblPlanningApps RIGHT JOIN tblPlanningHistory ON
tblPlanningApps.PlanningAppID =
tblPlanningHistory.PlanningAppID
WHERE (((tblPlanningHistory.PlanningHistoryID)=
[CurrentStatus]))
ORDER BY tblPlanningApps.ProjectID,
tblPlanningApps.DateAdPublication,
tblPlanningApps.DateLodged;
//END OF QUERY #1
QUERY #2
SELECT
PA.PlanningAppID,
"P" & Format(PA.PlanningAppID, "00000") AS INTERNALREF,
PH.PlanningHistoryID,
PA.ProjectID,
(SELECT Format(PR.ProjectNum,"0000") & " - " & PR.Client
& " @ " & PR.Loc_Street & ", " & PR.Loc_Town & ", " &
(SELECT County FROM tblCountys AS C WHERE
PR.CountyID=C.CountyID) FROM tblProjects AS PR WHERE
PA.ProjectID = PR.ProjectID) AS PDETAIL,
PlanningDesc,
PlanningRef,
NewspaperID,
Applicant,
AdWording,
DateAdPublication,
DateLodged,
PlanningStatus,
PermSought,
DateSiteNotice,
PA.Notes,
PA.NextDueItem,
PA.NextDueDate,
PA.CurrentStatus,
PA.PreparedBy,
(SELECT Initials FROM tblEmployees AS E WHERE
E.EmployeeID=PA.PreparedBy) AS PrepInitials,
bActive,
PH.PlanningAppID,
PH.State,
PS.EndState AS bEndState,
PS.bPositiveOutcom AS bPosOutcome,
PS.bOurResponsibility,
PH.DateEntered,
PH.Notes AS StateComments
FROM tblPlanningApps AS PA, tblPlanningHistory AS PH,
tblPlanningStates AS PS
WHERE (PA.PlanningAppID = PH.PlanningAppID) AND
(PS.PlanningStateID = PH.State);
//END OF QUERY #2