Query Replace Function

[bs0d]

I'm building a query in MS Access.

I have a time series of data (table 'A'), and comments with dates in a separate table (table 'B'). I have built a query to show data for an item, and the comments from Table B on the appropriate date where a matching item ID is found.

Is it possible for me to put in the max value of a given field from table 'A' at each point where a comment is currently shown from table B in the query?

I was thinking you could use replace(), but it's getting an error. I tried:

Expr1: Replace([table_b].[comment], "*", Max([table_a].[field]))

 

[bs0d]

Ok so a quick fix was changing the IIF condition to id_table_a = id_table_b

I tested this at first in Access, but wasn't getting the desired results. It turns out, I was using the wrong ID to test. Thanks again

 

[Joe4]

The SQL code of your query should look something like this:

SELECT 
    Table_A.Date, 
    Table_A.Value, 
    IIf([Table_B].[Comment] Is Not Null,DMax("Value","Table_A"),"") AS Comment
FROM 
    Table_A 
LEFT JOIN 
    Table_B 
ON 
    Table_A.Date = Table_B.Date;