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]))

 

[Joe4]

Perhaps DMAX is what you are after (http://www.techonthenet.com/access/functions/domain/dmax.php)?
If you cannot get it to work, trying posting a sample of your data and expected result.

 

[bs0d]

Ok lets assume the example data:

Table A:
fields: ID, Date, Value

example records:
ID   Date          value
1    01/01         12
2    01/02         11
3    01/03         154
4    01/04         68
5    01/05         42

Table B:
fields: ID, Date, Comment

Example records:
ID    Date        Comment
1      01/02      the cheese is old and moldy
2      01/05      opening season is today

Using the example table data above, I'd like to create a query that generated this result:

ID   Date          value      Comment
1    01/01         12          
2    01/02         11          154
3    01/03         154
4    01/04         68
5    01/05         42          154

The query displays the max value from the "value" field in Table A at each instance of a comment in Table B (for the appropriate date(s)).

 

[Joe4]

DMAX with an IIF function should work. Give it a shot, and write back if you get stuck.

 

[xenou]

From Post #3, why are comments "154" in the result. For one, there is no comment field with a value of 154. And why should dates 1/2 and 1/5 in relation to the comment table should give that result. Also, if there is only one comment on each day then why do we need max at all? This is unclear to me - the problem doesn't seem to be stated correctly. I don't think the sample data is illustrating what needs to happen.

 

[bs0d]

You're right, thanks Joe4.

In case anyone else finds the thread useful, here's basically what I used:

Expr1: IIF([table_a].[value] Is Not Null, DMax("value", "table_a", "ID=XXX"))

Thought I'd provide some background as to why I would want to do this.

I'm using an Excel chart to plot historical values. And I wanted to have a 2nd series that displayed a node / symbol where a comment existed. I wanted all of the comment nodes to appear in linear fashion across the top of the chart. To do this, I needed the max value for the item in the series to plot the data points. I could have done this in VBA, but though it would be much easier if I could craft the query to do it for me.

I apologize xenou if the question wasn't clear enough through my example(s). I may have answered your question with the background info above. The "154" appears instead of the comment if a comment existed for that date. The 154 is the max value from table a. I need/wanted the max value (instead of the comment) for charting purposes only.

 

[Joe4]

Good job! Glad you were able to figure it out. I thought you would be able to if I put you on the right path.

 

[bs0d]

There is a catch as I review the results...

It's providing what I need for each date that a comment exists in table_b, even if the ID's do not match. In other words, this works if theres only one entity that has comments. But since both tables house data for multiple entities, it's acting up a bit.

 

[Joe4]

Have you linked your two tables on the date field in your query?

 

[bs0d]

No ...