That will happen in the window. Access will not retain formatting very
well in SQL. Something I really don't like.
If you run just this as a query does it identify records you want to update
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_FROM
WHERE R.Run_WayPoint_ID_FROM is Null
How about running this one does it identify records you want to update?
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_TO
WHERE R.Run_WayPoint_ID_TO is Null
IF not, then I need to examine the logic I am using. Those two queries
should respectively return all tbl_WayPoints records that do not have a
match in the tbl_Road_Restrictions based on matching Run_WayPoint_ID to
the corresponding Run_WayPoint_ID_To and Run_WayPoint_Id_From values.
When you RUN the query, how are you doing so? Are you selecting Query:
Run from the menu, clicking on the Run button (Red exclamation)? If you
are simply switching views to the datasheet view, then no records will
be updated.
Do you get a message that zero records will be updated? Or that 5766
records will be updated? Or no message at all?
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
John,
When I run your query, it updates 5766 rows, but nothing happens, no data is
changed.
My (original) query updates 1783 rows, data is changed correctly (but
slowly...).
I noticed in the QBE window that the two In Select lines are on the two
Criteria rows (as expected) but they are each respectively one continouse
line, like this:
Line1: In (SELECT W.Run_WayPoint_ID FROM tbl_WayPoints W LEFT JOIN
tbl_Road_Restrictions R ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_FROM
WHERE R.Run_WayPoint_ID_FROM is Null)
Line2: In (SELECT W.Run_WayPoint_ID FROM tbl_WayPoints W LEFT JOIN
tbl_Road_Restrictions R ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_TO
WHERE R.Run_WayPoint_ID_TO is Null)
is this correct?
:
Fix my typo. I should have type Run_WayPoint_ID in place of Tbl_Waypoints_ID
UPDATE tbl_Waypoints
SET tbl_Waypoints.Restriction_Flag = 0
WHERE tbl_Waypoints.Run_waypoint_ID Is null
OR Run_Waypoint_ID in (
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_FROM
WHERE R.Run_WayPoint_ID_FROM is Null)
OR Run_WayPoint_ID in (
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_TO
WHERE R.Run_WayPoint_ID_TO is Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
efandango wrote:
John,
when I run that query I get a parameter box asking for 'Tbl_Waypoints_ID'
I'm not sure what do do?
:
PERHAPS the following will work for you.
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.
Without a backup you cannot restore the data if this does not work the way you
expect.
UPDATE tbl_Waypoints
SET tbl_Waypoints.Restriction_Flag = 0
WHERE tbl_Waypoints.Run_waypoint_ID Is null
OR Tbl_Waypoints_ID in (
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_FROM
WHERE R.Run_WayPoint_ID_FROM is Null)
OR Tbl_Waypoints_ID in (
SELECT W.Run_WayPoint_ID
FROM tbl_WayPoints W LEFT JOIN tbl_Road_Restrictions R
ON W.Run_WayPoint_ID = R.Run_WayPoint_ID_TO
WHERE R.Run_WayPoint_ID_TO is Null)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
efandango wrote:
Jeff, (anyone) can you tell me how to deal with this 'Cartesian' problem.
I can't really avoid the situation of having the two tables unjoined as the
only time they relate to each other is when I am tring to perform the
function of the query that I pasted in my previous post.
:
(This form/table setup is essentially to record key intersections on certain
main roads.)
I am trying to cross reference any of 2 (sub) streets to a master address.
The main address is on a continuous form, and the other sub addresses are on
another conttinuous form. Everytime a new pair of sub addresses are created
the query checks against the master address for any occurance of either (or
both) sub addresses and flags the record accordingly with a yes/no. This
way, as I work through the various main records, I can see at a glance
whether any given main road has a relavent junction.
:
It all starts with the data ... and you haven't described it.
If you add two tables to a query and DON'T join them, you will get every
possible combination of values from table1 TIMES table2. It's called a
Cartesian Product, and is only useful in a few rare circumstances. Usually,
folks avoid it like the plague!
Can you describe a bit more about "what" you want to accomplish, rather than
"how" you think you need to do ... something?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I think the problem is that two tables are not joined by any PK, which is
something that I can't do anything about. Am i stuck with a very slow
query,
or is there a solution?
UPDATE tbl_Road_Restrictions, tbl_Waypoints SET
tbl_Waypoints.Restriction_Flag = 0
WHERE (((tbl_Waypoints.Run_waypoint_ID)<>[Run_waypoint_ID_From] Or
(tbl_Waypoints.Run_waypoint_ID)<>[Run_waypoint_ID_To] Or
(tbl_Waypoints.Run_waypoint_ID) Is Null));
