Query returning wrong value.

[kim]

I have a query that calculates a value based on data
pulled from two tables, like this:
[TableA Sample_ID] [TableA data1] [Tablea data2] [Tabelb
data1] [tableb data2] [Expr1]

The tables are linked in a one to many relationship by the
sample ID field. I am trying to create a query that will
return data from a third table plus the calculated value
from query one, like this:
[TableC Sample_ID] [TableC data1] [TableC data2]
[Query1_Expr1]


Not all records in Table C have corresponding data used in
the expression in query 1, so some return an #error. How
can I hide the error statement? I have very limited
experience with access and no experience writing code, so
try to use little words if possible

 

[amdrew]

How about using a Join as follows:-

SELECT [TableC Sample_ID], [TableC data1], [TableC
data2], [Query1_Expr1]
FROM TableC LEFT JOIN Query1 ON [TableC Sample_ID] =
[Query1 Sample_ID]

Using a LEFT JOIN will give all records from TableC and
any corresponding records from Query1. Using RIGHT JOIN
instead will do the reverse: all records from Query1 and
corresponding records from TableC. Using INNER JOIN will
give only related records from both tables.

Hope this helps.

Andrew