How to construct a valid FindFirst statement when two fields have the same name after an Inner Join?

Question

I create a DAO.Recordset thus:

Set recJoined=MyDB.OpenRecordSet(" Select * From TableA Inner Join TableB On TableA.FieldA=TableB.FieldA")

The problem is that the Tables A and B have many fields, and some of them with the same name.

I now want to select a record with

recJoined.FindFirst FieldA="100"

This fails of course because 'FieldA' does not refer to a unique field. However when I change the criteria to:

recJoined.FindFirst TableA.FieldA="100"

I run into a 'unknown or invalid field reference' error.

I could specify an alias for the TableA.FieldA field of course in the Select statement, but as I need all fields from TableA and TableB and they have a lot, that would be very cumbersome indeed.

How to solve this?

Rather than doing a `FindFirst` on the result set, why are you not applying this filter in the `where` clause of the query? — Damien_The_Unbeliever, Nov 29 '11 at 08:18

score 2 · Answer 1 · edited May 23 '17 at 10:34

2

There are two ways to deal with this.

1 - Don't use SELECT *. Always specify the columns that you need. (typically same named columns are on the join columns which typically contain the same data) If you really need the same named column twice then you can alias them so its easy to predict what the name will be.

Select TableA.FieldA as TableA_FieldA, 
       TableB.FieldA as TableB_FieldA...

then you can do

 recJoined.FindFirst "TableA_FieldA='100'"

2 - If you must use SELECT * You can find out what the field names are first in a scratch and then use whatever the name is that the database engine assigned it and then use those result

 Dim fld As DAO.Field
 For Each fld In recJoined.Fields
    Debug.Print fld.Name
  Next fld

With MS Access or SQL Server this will produce something like

 TableA.FieldA
 FieldB
 ...
 TableB.FieldA
 FieldC

Which means you can do this

  recJoined.FindFirst "TableA.FieldA='100'"

edited May 23 '17 at 10:34

Community

1
1

answered Nov 29 '11 at 07:52

Conrad Frix

51,984
12
96
155

"don't use select * This will enable you to alias the columnn names" -- maybe you just phrased it poorly but to clarify: avoiding `SELECT *` does not enabled `AS` clauses ("column aliases"). Consider that `SELECT *, TableA.FieldA AS TableA_FieldA, ...` is valid and useful syntax. – onedaywhen Nov 29 '11 at 08:37
@onedaywhen Writing "Don't use select *" was a bit much I agree, however you should also note `SELECT *, TableA.FieldA AS TableA_FieldA` doesn't work on All DBs (Oracle for example). – Conrad Frix Nov 29 '11 at 15:29
@onedaywhen I propose you make your comment an answer. It is the closest think to a solution yet. – Dabblernl Nov 29 '11 at 16:01
@Dabblernl: I disagree: I think you should go with *this* answer. The main trust is: if you can't trust your SQL product to disambiguate column names then you have to do it yourself by writing out the columns with `AS clauses` in longhand. I've upvoted this one myself :) – onedaywhen Nov 30 '11 at 08:16

score 0 · Accepted Answer · answered Nov 30 '11 at 12:12

I upvoted Conrad's answer, but I cannot accept it as it does not address my specific need: to be able to select all fields, without specifically knowing which these are, but being able to alias the Join column.

(typically same named columns are on the join columns which typically contain the same data)

This is true of course, but the FindFirst statement does not see it so, it requieres a unique specifier and raises an error.

2 - If you must use SELECT * You can find out what the field names are first in a scratch and then use whatever the name is that the database engine assigned it and then use those result

The name simply is FieldA without any further qualifications or prefixes. So that does not help.

Consider that SELECT *, TableA.FieldA AS TableA_FieldA, is valid

Yes it is! and this leads to the solution. But the resultset has the FieldA now twice: once with the Aliased name, once with the original name. The Aliased field is updatable, the original field not. Once I took care not to update the FieldA field all was fine.

How to construct a valid FindFirst statement when two fields have the same name after an Inner Join?

2 Answers2