updatable join queries

  • Thread starter Thread starter Chris
  • Start date Start date
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
 
Hi,


Try to move the where criteria involving two tables into the FROM clause:


from

---------------------------------------------------------------------------
FROM tblPlanningApps AS PA, tblPlanningHistory AS PH,
tblPlanningStates AS PS

WHERE (PA.PlanningAppID = PH.PlanningAppID) AND
(PS.PlanningStateID = PH.State);
----------------------------------------------------------------------------
-


to

----------------------------------------------------------------------------
-
FROM ( tblPlanningApps AS PA
INNER JOIN tblPlanningHistory AS PH
ON PA.PlanningAppID = PH.PlanningAppID )
INNER JOIN tblPlanningStates AS PS
ON PS.PlanningStateID = PH.State
----------------------------------------------------------------------------
-



Sure, you can't edit computed expressions, but the query may be now
updateable.



Hoping it may help,
Vanderghast, Access MVP






Chris said:
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
Click to expand...
 
Back
Top