expression in query

[Todd]

I've got two tables (table1 and table2) I want to use in a query and their
relationship is linked by company number and it's a one to many relationship.
The linking fields are labeled the same (numCompanyNumber). The problem
I've got is that I have an expression I'd like to use in the query to show
project numbers in a certain format. The formatted expression in the query
field is as follows:

mgcid: [numCompanyNumber] & "-" & Format([submissiondate],"mm") & "-" &
Format([submissiondate],"yy") & "-" & Format([AEID],"0000")

Example of the format above: 4-07-08-0001 (the 0001 is an incrementing
number)

This expression works fine in the form and reports I use but in the query I
get an error because both tables have the same numCompanyNumber field and the
error says that it "refers to more than one table listed in the FROM clasue
of your SQL statement." I want to use table1 because it lists the company
name that I want to include in the query and not just the company number.

My questions finally is how can I specify in the query to direct the
expression to find only one of the two fields that are named the same?

 

[Michel Walsh]

Use the syntax


tableName.fieldName


to specify which of the two tables you want.


Vanderghast, Access MVP

 

[Todd]

Thanks! I tried that initially but had brackets around the table name. This
time I just had it all in the brackets [table1.fieldname] like you suggested.
--
Todd

Use the syntax


tableName.fieldName


to specify which of the two tables you want.


Vanderghast, Access MVP

I've got two tables (table1 and table2) I want to use in a query and their
relationship is linked by company number and it's a one to many
relationship.
The linking fields are labeled the same (numCompanyNumber). The problem
I've got is that I have an expression I'd like to use in the query to show
project numbers in a certain format. The formatted expression in the
query
field is as follows:

mgcid: [numCompanyNumber] & "-" & Format([submissiondate],"mm") & "-" &
Format([submissiondate],"yy") & "-" & Format([AEID],"0000")

Example of the format above: 4-07-08-0001 (the 0001 is an incrementing
number)

This expression works fine in the form and reports I use but in the query
I
get an error because both tables have the same numCompanyNumber field and
the
error says that it "refers to more than one table listed in the FROM
clasue
of your SQL statement." I want to use table1 because it lists the company
name that I want to include in the query and not just the company number.

My questions finally is how can I specify in the query to direct the
expression to find only one of the two fields that are named the same?