Cannot access fields using recordset property when doing a self-join 
Author Message
 Cannot access fields using recordset property when doing a self-join

I have an Access SQL statement that does three self joins.
The results give me four different names to this table

Name_table_NEW
Name_table_NEW_1
Name_table_NEW_2
Name_table_NEW_3

SELECT Name_table_NEW.Primary_key, Name_table_NEW_1.Primary_key,
Name_table_NEW_2.Primary_key, Name_table_NEW_3.Primary_key
FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT JOIN
Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
Name_table_NEW_3.Reports_direct_to
WHERE (((Name_table_NEW.Primary_key)=2));

Using VBScript (ASP)   I'm trying to access these fields using the recordset
property:
        rs.fields("Primary_key").value
but the fieldnames are the same for all three tables, and it won't let me
prefix the field I want with the corresponding table name (I didn't think
this would work anyway),
        rs.fields("Name_table_NEW_1.Primary_key").value
but I don't know how else to get to these fields.

Any suggestions?
Thanks.

Cathy



Tue, 11 Dec 2001 03:00:00 GMT  
 Cannot access fields using recordset property when doing a self-join

Cathy,

You need to give the fiels different names using the AS predicate. Look it up in
the DAO help. Basicaly, it goes something like this:

Name_table_NEW.Primary_key AS Primary_KeyOfTable_New... and so on.

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please post/reply to the newsgroup(s) so
that everyone can benefit from the discussion.

Klaus H. Probst, MCP

       ICQ: 22454937
      The VB Box: http://members.xoom.com/kprobst/
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Quote:

>I have an Access SQL statement that does three self joins.
>The results give me four different names to this table



Tue, 11 Dec 2001 03:00:00 GMT  
 Cannot access fields using recordset property when doing a self-join
Like this:

SELECT Name_table_NEW.Primary_key As PK1,
Name_table_NEW_1.Primary_key As PK2,
Name_table_NEW_2.Primary_key As PK3,
Name_table_NEW_3.Primary_key As PK4

FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT JOIN
Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
Name_table_NEW_3.Reports_direct_to

WHERE (((Name_table_NEW.Primary_key)=2));

--
Kipp Woodard
Access MCP, MVP

http://www.mesahq.com

Quote:

> I have an Access SQL statement that does three self joins.
> The results give me four different names to this table

> Name_table_NEW
> Name_table_NEW_1
> Name_table_NEW_2
> Name_table_NEW_3

> SELECT Name_table_NEW.Primary_key, Name_table_NEW_1.Primary_key,
> Name_table_NEW_2.Primary_key, Name_table_NEW_3.Primary_key
> FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
> Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT JOIN
> Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
> Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
> Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
> Name_table_NEW_3.Reports_direct_to
> WHERE (((Name_table_NEW.Primary_key)=2));

> Using VBScript (ASP)   I'm trying to access these fields using the
recordset
> property:
>         rs.fields("Primary_key").value
> but the fieldnames are the same for all three tables, and it won't let me
> prefix the field I want with the corresponding table name (I didn't think
> this would work anyway),
>         rs.fields("Name_table_NEW_1.Primary_key").value
> but I don't know how else to get to these fields.

> Any suggestions?
> Thanks.

> Cathy



Tue, 11 Dec 2001 03:00:00 GMT  
 Cannot access fields using recordset property when doing a self-join
Thanks for your reply, I have one more question related to this.

Is there a way to make the query put the results in one single field?
Like instead of having it display each field seperately merge all of them
into one.  I started to try a union query but was lost from the beginning.

Thanks again.

Quote:

>Like this:

>SELECT Name_table_NEW.Primary_key As PK1,
>Name_table_NEW_1.Primary_key As PK2,
>Name_table_NEW_2.Primary_key As PK3,
>Name_table_NEW_3.Primary_key As PK4

>FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
>Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT JOIN
>Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
>Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
>Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
>Name_table_NEW_3.Reports_direct_to

>WHERE (((Name_table_NEW.Primary_key)=2));

>--
>Kipp Woodard
>Access MCP, MVP

>http://www.mesahq.com


>> I have an Access SQL statement that does three self joins.
>> The results give me four different names to this table

>> Name_table_NEW
>> Name_table_NEW_1
>> Name_table_NEW_2
>> Name_table_NEW_3

>> SELECT Name_table_NEW.Primary_key, Name_table_NEW_1.Primary_key,
>> Name_table_NEW_2.Primary_key, Name_table_NEW_3.Primary_key
>> FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
>> Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT
JOIN
>> Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
>> Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
>> Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
>> Name_table_NEW_3.Reports_direct_to
>> WHERE (((Name_table_NEW.Primary_key)=2));

>> Using VBScript (ASP)   I'm trying to access these fields using the
>recordset
>> property:
>>         rs.fields("Primary_key").value
>> but the fieldnames are the same for all three tables, and it won't let me
>> prefix the field I want with the corresponding table name (I didn't think
>> this would work anyway),
>>         rs.fields("Name_table_NEW_1.Primary_key").value
>> but I don't know how else to get to these fields.

>> Any suggestions?
>> Thanks.

>> Cathy



Tue, 11 Dec 2001 03:00:00 GMT  
 Cannot access fields using recordset property when doing a self-join
Hi Cathy,
Try something like:
"select fielda & fieldb & fieldc as fieldabc from tablename"
HTH,
Dan

Quote:

>Thanks for your reply, I have one more question related to this.

>Is there a way to make the query put the results in one single field?
>Like instead of having it display each field seperately merge all of them
>into one.  I started to try a union query but was lost from the beginning.

>Thanks again.


>>Like this:

>>SELECT Name_table_NEW.Primary_key As PK1,
>>Name_table_NEW_1.Primary_key As PK2,
>>Name_table_NEW_2.Primary_key As PK3,
>>Name_table_NEW_3.Primary_key As PK4

>>FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
>>Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT JOIN
>>Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
>>Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
>>Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
>>Name_table_NEW_3.Reports_direct_to

>>WHERE (((Name_table_NEW.Primary_key)=2));

>>--
>>Kipp Woodard
>>Access MCP, MVP

>>http://www.mesahq.com




- Show quoted text -

Quote:
>>> I have an Access SQL statement that does three self joins.
>>> The results give me four different names to this table

>>> Name_table_NEW
>>> Name_table_NEW_1
>>> Name_table_NEW_2
>>> Name_table_NEW_3

>>> SELECT Name_table_NEW.Primary_key, Name_table_NEW_1.Primary_key,
>>> Name_table_NEW_2.Primary_key, Name_table_NEW_3.Primary_key
>>> FROM ((Name_table_NEW LEFT JOIN Name_table_NEW AS Name_table_NEW_1 ON
>>> Name_table_NEW.Primary_key = Name_table_NEW_1.Reports_direct_to) LEFT
>JOIN
>>> Name_table_NEW AS Name_table_NEW_2 ON Name_table_NEW_1.Primary_key =
>>> Name_table_NEW_2.Reports_direct_to) LEFT JOIN Name_table_NEW AS
>>> Name_table_NEW_3 ON Name_table_NEW_2.Primary_key =
>>> Name_table_NEW_3.Reports_direct_to
>>> WHERE (((Name_table_NEW.Primary_key)=2));

>>> Using VBScript (ASP)   I'm trying to access these fields using the
>>recordset
>>> property:
>>>         rs.fields("Primary_key").value
>>> but the fieldnames are the same for all three tables, and it won't let
me
>>> prefix the field I want with the corresponding table name (I didn't
think
>>> this would work anyway),
>>>         rs.fields("Name_table_NEW_1.Primary_key").value
>>> but I don't know how else to get to these fields.

>>> Any suggestions?
>>> Thanks.

>>> Cathy



Tue, 11 Dec 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Self Joins in Access

2. Doing Join, how to reference common fields?

3. Can't update field in recordset using WSH (Access Database)

4. ADO- resizing existing fields (cannot be done in Visdata)

5. Can't access value property unless field part of recordset

6. Question:Access field properties using DAO?

7. Changing Access Database Field Properties using VB

8. Recordset Field type properties?

9. ASP variant cannot use those ADODB.Recordset property

10. ASP variant cannot use those ADODB.Recordset property

11. Question on Problem encountered w/ using ADODC DataControl.Recordset Property w/ ADODB.Recordset objects

12. Cannot set filter property on Recordset.

 

 
Powered by phpBB® Forum Software