Problem with query and joining tables

[jfca283]

Hi
Im' designing a simple query.
I have two tables, T1 and T2.
T1 has many fileds, but one in common with T2.
The field is "mxx", which represents the first day of the month for that record.
T2 also has the filed mxx, as was specified, and anothe field named Tri.
For mxx=1/1/10, Tri=def 10; mxx=1/2/12, Trim=efm 12; mxx=1/6/14, Tri=jas 14 and so on.
Every date for mxx is represented in the Tri field on T2.
So, Tri represents the month of the data and the pre and post month also and the year.
T2 for that reason, has only 50 records. T1 over 1000.


So, here is my question
How do i design a query with fields from T1 and the field Tri from T2?
I created the query, and drag from T1 to T2 the field mxx, and i added to the fields asked on the query Tri from T2.
But i get an error. If mxx from T2 to T1, the same result.

[John_G]

What is the error you are getting? We can't answer your question if we don't know what the problem is!

John

[jfca283]

Something like this
"It may be the fiedl [mxx] specified makes reference to more than one of the tables shown From of the SQL instruction."
I use Access in spanish. That's why the message is aprox to this
Puede que el campo [mxx] especificado haga referencia a más de una tabla de las mostradas en la cláusula FROM de la instrucción SQL.

[John_G]

OK - it's an easy fix.

What (I think) is happening is that the Select... part of the SQL refers to the field mxx without stating which table it is to select from.

In your query or SQL, whenever you are referring to the Mxx field, you have to state explicitly which table you are referring to, because mxx is in both. The syntax is this:

[T1].[mxx] or [T2].[mxx]

That should fix it.

John

[jfca283]

I did it and i get the same error.
The query has some fields calculated from T1.
But when i run the query without those fields, which are computed, the query goes ok.

[John_G]

You still have to qualify each use of mxx with the table name; this includes formulas in calculated fields, e.g. [T1].[mxx] * 1.05

John

[jfca283]

That's the strange.
The calculated fields have the references to the table T1.
And when i add Tri to the query i'm planning to do, i did refer to the T2 table.
And the error. The same error over and over again.