Problem with Field that Refers to More than One Table 1

[ashmanuk]

Hi there,

I have a database set up with a field that is used for two different entrys, depending on the relationship it has with another table. I am trying to create a report that displays this field in both of its contexts.

So far I have set up two tables with the field in so the field can be displayed in both contexts and got a query to run bringing up the desired data.

The problem is that now I have created the report to show the data in, access gives me an error saying that the field could refer to more than one table in the FROM clause of my SQL. As you can see from my SQL below, I have stated the separate fields and tables to darw them from (the field in question is ow_name) and the stranger thing is that the query runs the SQL fine, its only the report that comes up with this error.

SELECT DISTINCT qry_ClientListwSyndMemOwner.ow_ref, qry_ClientListwSyndMemOwner.ow_name, qry_ClientListwSyndMemOwner.ow_obs1, qry_ClientListwSyndMemOwnlnk.ol_orefx, qry_ClientListwSyndMemOwnlnk.ol_perc, qry_ClientListwSyndMemOwner_1.ow_name
FROM (qry_ClientListwSyndMemOwner INNER JOIN qry_ClientListwSyndMemOwnlnk ON qry_ClientListwSyndMemOwner.ow_ref=qry_ClientListwSyndMemOwnlnk.ol_oref) INNER JOIN qry_ClientListwSyndMemOwner_1 ON qry_ClientListwSyndMemOwnlnk.ol_orefx=qry_ClientListwSyndMemOwner_1.ow_ref;

Any ideas?

 

[vanillapod]

can't you put the table name of the field you want b4 the fieldname table:myfieldname? or something similar?

 

[ashmanuk]

Yes, if you have a look at my SQL I have already done this:

qry_ClientListwSyndMemOwner.ow_name is the first table and

qry_ClientListwSyndMemOwner_1.ow_name is the second.

(Lines 2 and 5 from original thread).

To be honest I think it must be more of a problem with the report than the code, as the query runs fine. The thing is I dont know what could possibly be wrong with the report, as even getting the wizard to create a report brings up the same error.

 

[vanillapod]

ooops sorry i didn't read it properly! How about trying to rename one of the fields in the query so the report doesn't see two fields with similar / same names?

 

[ashmanuk]

How can you rename a field in a query?

 

[vanillapod]

newfieldname:[mytable.myoldfieldname]

the output from the query will then have a field called newfieldname

 

[ashmanuk]

Sorry, where does this bit of code go?

Thanks

 

[vanillapod]

sorry it goes in the field row of the column you want to have the new name assigned to.

So maybe you'd have:

ow_nameNEW:[qry_ClientListwSyndMemOwner_1.ow_name]

 

[ashmanuk]

Yes, its worked!

Thanks a lot for that one mate!

 

[vanillapod]

no worries, nice to help someone else out for a change!