Change column name

[asu81]

Hi!
I'm very new to Access and I've got a problem.

I have a query looking like this:

SELECT *
FROM tblA INNER JOIN qryA ON tblA.ProjectNo= qryA.ProjectNo;

It is supposed to select only the rows from tblA which has a ProjectNo equal to any of the values of the ProjectNo column in qryA (the only existing column in that query).

My problem is that this results in my ProjectNo column being named "tblA.ProjectNo" and another column named "qryA.ProjectNo" is added in the far right of the query.

The contents of these columns is the same, ie project numbers. Apart from this above problem the query works as it should.

Can anyone please give me advice on how to solve this?
Thanks!

 

[pr2-eugin]

When performing Joins in Queries each column name should be ambiguous, so Access automatically assigns the Column name as tableName.ColumnName.. So to avoid that.. you have to manually include the Columns you want and create alias; avoiding the *... Something like..

SELECT tableName.Field1 [COLOR=Red]AS [/COLOR]FirstName anotherTable.Field2 [COLOR=Red]AS[/COLOR] LastName
FROM tableName INNER JOIN anotherTable ON tableName.commonField = anotherTable.sameColumn;

The keyword AS changes the column name tableName.Field1 to FirstName.. Hope this helps..

 

[Galaxiom]

Select * means all fields so you will get the join field from both tables. Specify the exact fields you require from each table.

Otherwise (assuming a join on field1):
SELECT table1.*, table2.field2, table2.field3

BTW. Unambiguous field names do not require the tablename to be included.

 

[asu81]

Thanks, that solved it!