Error with creating a report. Same fieldname used twice on same query.

[esung911]

Here's the scenario, I have two tables, two queries. Table A contains
two fields, Name and Location. Table B has Project Name, Project
Lead, Project Lead Backup. I built Query A to select everything from
Table A.

Query B selects from Table B, and links Project Lead-Name from Query
A, and Project Lead Backup-Name from Table A.
Now it looks like Project Name, Query A.Name, Query A.Location, Table
A.Name, Table A.Location.

My goal is to show Location of the Project Lead and the Backup. This
query works fine. Now the problem is the report. My report data
source points to Query B. But whenever I run it, I get field could
refer to more than fields in the FROM statement (or something on that
line).

There's got to be a better way of doing this without creating another
table. Please help. Thank you

 

[Damian S]

Hi,

Update your query so that the fields that have the same name are given their
own alias, like this:

select tableA.name as TableAName, tableB.name as TableBName from tableA
inner join tableB on tableA.ID = tableB.ID

As an aside, you should really avoid using keywords (name) as the name of an
object in your database, ie: table, field, control etc...

Hope this helps.

Damian.

 

[esung911]

Thanks, I looked up how to alias. Another way of doing it that I found
was to go to design view and just rename the fieldname within the
query so that it looks like this.

NewFieldName:OldFieldName

Thanks for your help!