Access left outer join or handling no records found 
Author Message
 Access left outer join or handling no records found

Hi everyone,

I needs some help again utilizing either a left outer join for my sql query
or to figure out how to handle sql queries that do not return any rows.
Here's my situation I have a list of events, some are created by sales
people some are not. Currently I have 3 tables that contain the information:
Event table (with event descriptions), Sales force table (with the sales
people) and a look up table that contains the sales person id and the event
id. The reason for this is that it is a many to many relationship. More than
one sales person can work on getting an event and sales people will create
many events. When displaying an event I need to get all the names of the
sales people who worked on it (one may have gotten the lead while another
closed it and they both should get credit) but sometimes there won't be a
sales person associated with it. If I use a query to get all the events and
then try to run a query where I get the names fromt he look up table, if it
doesn't exist then I get an error

ADODB.Fields error '800a0cc1'

ADO could not find the object in the collection corresponding to the name or
ordinal reference requested by the application.

I've tried to create a Left Outer Join, but I can't figure out how to join
more than one table: Both the look up and the sales force tables.

I would love an answer for both if it's possible.

Thanks,
Stephanie



Fri, 22 Oct 2004 08:12:22 GMT  
 Access left outer join or handling no records found
This is hard to see without the code and (for me) some knowledge of your table
structure.

I can tell you how to do the error handling, though; that's straightforward.

On Error Resume Next ' hands error handling over to you

' do stuff that might generate an error here
'
If Err.Number <> 0 Then
' do your stuff for case where error happened
Err.Clear ' resets Err object
Else
' do your stuff for not having an error here
End If

On Error Goto 0 ' turns internal error handling back off


Quote:
> Hi everyone,

> I needs some help again utilizing either a left outer join for my sql query
> or to figure out how to handle sql queries that do not return any rows.
> Here's my situation I have a list of events, some are created by sales
> people some are not. Currently I have 3 tables that contain the information:
> Event table (with event descriptions), Sales force table (with the sales
> people) and a look up table that contains the sales person id and the event
> id. The reason for this is that it is a many to many relationship. More than
> one sales person can work on getting an event and sales people will create
> many events. When displaying an event I need to get all the names of the
> sales people who worked on it (one may have gotten the lead while another
> closed it and they both should get credit) but sometimes there won't be a
> sales person associated with it. If I use a query to get all the events and
> then try to run a query where I get the names fromt he look up table, if it
> doesn't exist then I get an error

> ADODB.Fields error '800a0cc1'

> ADO could not find the object in the collection corresponding to the name or
> ordinal reference requested by the application.

> I've tried to create a Left Outer Join, but I can't figure out how to join
> more than one table: Both the look up and the sales force tables.

> I would love an answer for both if it's possible.

> Thanks,
> Stephanie



Fri, 22 Oct 2004 08:46:12 GMT  
 Access left outer join or handling no records found
Thanks. Here's the table stucture:

Event:
Event id
Name
Date
Address... etc..

Sales People:
Sales id
Name
phone
address
Region... etc.

Sales_events (cross reference table)
Sales id
Event id

Here's one way to try to get the info from these three tables:

SQL_query = "select * from Event"
Set ERS = MyConn.Execute(SQL_query)

WHILE NOT ARS.EOF
SQL_query_sp = "Select * from [sales people] as sp, sales_events as se where
se.[event id] = " & ERS("event id") & " and sp.[sales id] = se.[salesid]"
Set SERS = MyConn.Execute(SQL_query)

Blah.. blah...
WEND

The problem is that sometimes the second query won't produce any rows and it
then dies with the error below. So I figure it would be easier to do a left
outer join but I don't know how to joing the two tables with all the
dependencies.

Also the error happens on the Set SERS part so I need to understand how to
capture that error. All the error handling routines I've seen happen after
that point.

Thanks,
Stephanie



Quote:
> This is hard to see without the code and (for me) some knowledge of your
table
> structure.

> I can tell you how to do the error handling, though; that's
straightforward.

> On Error Resume Next ' hands error handling over to you

> ' do stuff that might generate an error here
> '
> If Err.Number <> 0 Then
> ' do your stuff for case where error happened
> Err.Clear ' resets Err object
> Else
> ' do your stuff for not having an error here
> End If

> On Error Goto 0 ' turns internal error handling back off



> > Hi everyone,

> > I needs some help again utilizing either a left outer join for my sql
query
> > or to figure out how to handle sql queries that do not return any rows.
> > Here's my situation I have a list of events, some are created by sales
> > people some are not. Currently I have 3 tables that contain the
information:
> > Event table (with event descriptions), Sales force table (with the sales
> > people) and a look up table that contains the sales person id and the
event
> > id. The reason for this is that it is a many to many relationship. More
than
> > one sales person can work on getting an event and sales people will
create
> > many events. When displaying an event I need to get all the names of the
> > sales people who worked on it (one may have gotten the lead while
another
> > closed it and they both should get credit) but sometimes there won't be
a
> > sales person associated with it. If I use a query to get all the events
and
> > then try to run a query where I get the names fromt he look up table, if
it
> > doesn't exist then I get an error

> > ADODB.Fields error '800a0cc1'

> > ADO could not find the object in the collection corresponding to the
name or
> > ordinal reference requested by the application.

> > I've tried to create a Left Outer Join, but I can't figure out how to
join
> > more than one table: Both the look up and the sales force tables.

> > I would love an answer for both if it's possible.

> > Thanks,
> > Stephanie



Fri, 22 Oct 2004 09:36:19 GMT  
 Access left outer join or handling no records found
OK, the table structure doesn't help me a lot either... :-(

But this should do the error handling for you; I'm changing your while... do to
a do while...loop structure to allow you to jump out on error.  I'm not sure
what your code does with the retrieved data, but I assume you process the
records in a loop if you don't get an error, then want to jump out if you do -
so that's how this is set up-

On Error Resume Next
DO WHILE NOT ARS.EOF
Set SERS = MyConn.Execute(SQL_query)
If Err.Number <> 0 Then
Err.Clear ' resets Err object
 Exit Do ' We jump out of the loop since there is nothing to retrieve
Else
' process records
End If
On Error Goto 0


Quote:
> Thanks. Here's the table stucture:

> Event:
> Event id
> Name
> Date
> Address... etc..

> Sales People:
> Sales id
> Name
> phone
> address
> Region... etc.

> Sales_events (cross reference table)
> Sales id
> Event id

> Here's one way to try to get the info from these three tables:

> SQL_query = "select * from Event"
> Set ERS = MyConn.Execute(SQL_query)

> SQL_query_sp = "Select * from [sales people] as sp, sales_events as se where
> se.[event id] = " & ERS("event id") & " and sp.[sales id] = se.[salesid]"
> Set SERS = MyConn.Execute(SQL_query)

> Blah.. blah...
> WEND

> The problem is that sometimes the second query won't produce any rows and it
> then dies with the error below. So I figure it would be easier to do a left
> outer join but I don't know how to joing the two tables with all the
> dependencies.

> Also the error happens on the Set SERS part so I need to understand how to
> capture that error. All the error handling routines I've seen happen after
> that point.

> Thanks,
> Stephanie



> > This is hard to see without the code and (for me) some knowledge of your
> table
> > structure.

> > I can tell you how to do the error handling, though; that's
> straightforward.

> > On Error Resume Next ' hands error handling over to you

> > ' do stuff that might generate an error here
> > '
> > If Err.Number <> 0 Then
> > ' do your stuff for case where error happened
> > Err.Clear ' resets Err object
> > Else
> > ' do your stuff for not having an error here
> > End If

> > On Error Goto 0 ' turns internal error handling back off



> > > Hi everyone,

> > > I needs some help again utilizing either a left outer join for my sql
> query
> > > or to figure out how to handle sql queries that do not return any rows.
> > > Here's my situation I have a list of events, some are created by sales
> > > people some are not. Currently I have 3 tables that contain the
> information:
> > > Event table (with event descriptions), Sales force table (with the sales
> > > people) and a look up table that contains the sales person id and the
> event
> > > id. The reason for this is that it is a many to many relationship. More
> than
> > > one sales person can work on getting an event and sales people will
> create
> > > many events. When displaying an event I need to get all the names of the
> > > sales people who worked on it (one may have gotten the lead while
> another
> > > closed it and they both should get credit) but sometimes there won't be
> a
> > > sales person associated with it. If I use a query to get all the events
> and
> > > then try to run a query where I get the names fromt he look up table, if
> it
> > > doesn't exist then I get an error

> > > ADODB.Fields error '800a0cc1'

> > > ADO could not find the object in the collection corresponding to the
> name or
> > > ordinal reference requested by the application.

> > > I've tried to create a Left Outer Join, but I can't figure out how to
> join
> > > more than one table: Both the look up and the sales force tables.

> > > I would love an answer for both if it's possible.

> > > Thanks,
> > > Stephanie



Fri, 22 Oct 2004 09:53:15 GMT  
 Access left outer join or handling no records found
Use this:

Set pobjRS = GetRecordset . . . .

With pobjRS
    If .RecordCount > 0 Then
        .MoveFirst
    Else
        'OK - If you want to do anything about no data then do it here.
    End If

    Do Until .EOF
         'Your Stuff

        .MoveNext
    Loop

End With

Set pobjRS = Nothing

This will only attempt to access the rowset information  if there is data in
the recordset. I have used this construct for 2 years without fail so far so
I'm pretty sure it's solid.

Although, I have heard rumours that certain cursors (forward only perhaps)
do not return a valid RecordCount. However, Do Until .EOF should catch this
anyway - the first If is really just extra incase someone has been messing
with the recordset first (eg. playing about with filters etc.). I sometimes
do that and then forget to stick the cursor back to the front of the
recordset.

Hope it helps,

Chris Barber.

NB: ***** With statement only valid in VBScript on the server after IE 5.x
has been installed (you can select just the VBScript update).



OK, the table structure doesn't help me a lot either... :-(

But this should do the error handling for you; I'm changing your while... do
to
a do while...loop structure to allow you to jump out on error.  I'm not sure
what your code does with the retrieved data, but I assume you process the
records in a loop if you don't get an error, then want to jump out if you
do -
so that's how this is set up-

On Error Resume Next
DO WHILE NOT ARS.EOF
Set SERS = MyConn.Execute(SQL_query)
If Err.Number <> 0 Then
Err.Clear ' resets Err object
 Exit Do ' We jump out of the loop since there is nothing to retrieve
Else
' process records
End If
On Error Goto 0


Quote:
> Thanks. Here's the table stucture:

> Event:
> Event id
> Name
> Date
> Address... etc..

> Sales People:
> Sales id
> Name
> phone
> address
> Region... etc.

> Sales_events (cross reference table)
> Sales id
> Event id

> Here's one way to try to get the info from these three tables:

> SQL_query = "select * from Event"
> Set ERS = MyConn.Execute(SQL_query)

> SQL_query_sp = "Select * from [sales people] as sp, sales_events as se
where
> se.[event id] = " & ERS("event id") & " and sp.[sales id] = se.[salesid]"
> Set SERS = MyConn.Execute(SQL_query)

> Blah.. blah...
> WEND

> The problem is that sometimes the second query won't produce any rows and
it
> then dies with the error below. So I figure it would be easier to do a
left
> outer join but I don't know how to joing the two tables with all the
> dependencies.

> Also the error happens on the Set SERS part so I need to understand how to
> capture that error. All the error handling routines I've seen happen after
> that point.

> Thanks,
> Stephanie



> > This is hard to see without the code and (for me) some knowledge of your
> table
> > structure.

> > I can tell you how to do the error handling, though; that's
> straightforward.

> > On Error Resume Next ' hands error handling over to you

> > ' do stuff that might generate an error here
> > '
> > If Err.Number <> 0 Then
> > ' do your stuff for case where error happened
> > Err.Clear ' resets Err object
> > Else
> > ' do your stuff for not having an error here
> > End If

> > On Error Goto 0 ' turns internal error handling back off



> > > Hi everyone,

> > > I needs some help again utilizing either a left outer join for my sql
> query
> > > or to figure out how to handle sql queries that do not return any
rows.
> > > Here's my situation I have a list of events, some are created by sales
> > > people some are not. Currently I have 3 tables that contain the
> information:
> > > Event table (with event descriptions), Sales force table (with the
sales
> > > people) and a look up table that contains the sales person id and the
> event
> > > id. The reason for this is that it is a many to many relationship.
More
> than
> > > one sales person can work on getting an event and sales people will
> create
> > > many events. When displaying an event I need to get all the names of
the
> > > sales people who worked on it (one may have gotten the lead while
> another
> > > closed it and they both should get credit) but sometimes there won't
be
> a
> > > sales person associated with it. If I use a query to get all the
events
> and
> > > then try to run a query where I get the names fromt he look up table,
if
> it
> > > doesn't exist then I get an error

> > > ADODB.Fields error '800a0cc1'

> > > ADO could not find the object in the collection corresponding to the
> name or
> > > ordinal reference requested by the application.

> > > I've tried to create a Left Outer Join, but I can't figure out how to
> join
> > > more than one table: Both the look up and the sales force tables.

> > > I would love an answer for both if it's possible.

> > > Thanks,
> > > Stephanie



Fri, 22 Oct 2004 16:13:25 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. DAO Left Outer Join Update Problem

2. Need info from table that is linked through another left outer join

3. SQL statement on 2 or more tables (left outer joins)

4. ****Left Outer Join

5. Left Outer Join to SQL Server

6. LEFT OUTER JOIN PROBLEMS!!!

7. Left Outer Joins

8. RIGHT / LEFT OUTER JOIN

9. LEFT (OUTER) JOIN problem...

10. LEFT OUTER JOIN

11. Outer Join with Where clause returns Unexpected Records

12. outer join with ACCESS

 

 
Powered by phpBB® Forum Software