Access field from multiple table join

[noxum]

I've created a recordset called rsTme with the following code:
"Select * from EmpInfo Left Join EmpTime ON EmpInfo.SSN = EmpTime.SSn Where EmpInfo.SSN = " & somenumber

Both the EmpInfo table and the EmpTime table have a SSN field and I now need to access the EmpTime's table's SSN Field. I've tried this:

With rsTme
if !EmpTime!SSN = "" then
....

This is where I get the error:
"Item cannot be found in the collection corresponding to the requested name or ordinal"

Does anyone know the syntax in VB6 to get reference a field name from a specified table in the recordset.

Noxum

 

[peekay]

Noxum
If both tables have a field with the same name it will not include that field in your recordset.
The best suggestion I can make is that you select the specific fields from each table that you need such as :

Select *.empinfo, anyfield.emptime from empinfo left join ....

When you do that SSN from the first table wll be included. Just make sure that you do not include SSN from the second table as well.

Hope it helps


PK Odendaal

http://www.odendaal.com

and pko@942.co.za

 

[noxum]

Thanks! Not the answer I was hoping for...But atleast now, I won't spend forever trying to figure it out...

Thanks
Noxum

 

[leiphasw]

You can get fields with the same name from multiple tables if you use the AS operator to rename the field in the result set:

adoPartyRosterRS.Open "select Members.ID AS MemberID, Visit.ID AS VisitID from Members INNER JOIN Visit ON Members.MembershipID = Visit.MemberID where Visit.PartyID = " & gPartyID, connPartyRoster, adOpenStatic, adLockPessimistic

 

[noxum]

Would this allow me to update each of the tables through this record set?

 

[peekay]

Yes, you can update the fields of both tables with one recordset.

PK Odendaal

http://www.odendaal.com

and pko@942.co.za

 

[LazyMe]

>>Select * from EmpInfo Left Join EmpTime

>>"Item cannot be found in the collection corresponding to the requested name or ordinal"

>>EmpTime!SSN


Isn't this simply because none of the fields of EmpTime is in the SELECT list?


SELECT i.*, t.SSN AS OtherSSN FROM EmpInfo i LEFT JOIN EmpTime t ON i.SSN=t.SSN

Greetings,
Rick