asked on 7/23/2008

Cannot Add New Record When Using A Left Join in Access

I am trying to add a a new record into my SQL 2000 table (using Access as the front end). I have a left join between the route and stops table. When I try and use the query tool in access, it does not allow be to add a new stop. That is all I want to do it add a new stop to a route. Please let me know what I am doing wrong. I am passing the query using ADO (in case anyone needs know). I DO NOT want to use split forms, this is all done through code.

SELECT Route.RouteID, Route.Route, STOPS.StopID, STOPS.STOP
FROM Route LEFT JOIN STOPS ON Route.Route = STOPS.ROUTE
WHERE (((Route.Route)="ABC"));

Last Comment

7/23/2008

7/23/2008

i think you need to add OUTER ie
LEFT OUTER JOIN

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

7/23/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

fhlio_admin

7/23/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Nickon17

7/23/2008

ASKER

The OUTER JOIN didn't wory, but both angelll and fhlio_admin are right, I cannot update with a LEFT JOIN. What I ended up doing was using this query:
SELECT STOPS.STOP, STOPS.ROUTE, STOPS.STOPID FROM STOPS
and then manually assigning the value of stops.route (my joined field to route.route) with the value of route.route (I had a stored in a txt box on my form). That worked for me, hope it helps someone else figure this out!

Nickon17

7/23/2008

ASKER

Thanks! I am splitting the points because both answers were acurate, and did help me understand fully that what I was doing wasn't going to work.