How do I change an entry depending on the value of another

[henniec]

Good Day,

I have two sets of tables. The input from the tables is imported from data loggers and based on time. The one table is straight forward with time running on a 24 hour cycle. The other one only record useful data when the system is in service.

I have to report the time at three different times and change the input in one a different column depending on specific times.

The one column can be either a Tx or a Vx depending if it is time reported or a value reported eg.

Column A Coulumn B (time)
T1 or V1 8:00
T2 or V2 12:00
T3 or V3 16:00

I do not have a problem get the required result. My question lies with the second table. Keeping the same times but now the operation only starts at say 11:00 and ends at 17:00. If I use the same iif statement I used for the first table the result will be

Column A Column B (time)
T2 or V2 12:00
T3 or V3 16:00

Instead of

Column A Column B (time)
T1 or V1 12:00
T2 or V2 16:00

How can I set the query so it first check if there was a reading on a specific date for 8:00? If yes the V1 or T1 for 8:00 and if not, then T1 = 12:00

Thank you for your time.

Hennie

 

[rac2]

I do not entirely understand what you are trying to do there. But it sounds like you wish to show different values for a given row depending on a value in another row.

The IIf() function cannot do that. It only works on values in a single row. So you must get the values from different rows into the same row using a JOIN. The JOIN might be a self-join with a WHERE clause to obtain the appropriate rows to link together, or it might be necessary to join the basic table with a subquery or a view.

You might think about further processing the imported data so that it is stored in a table with a structure that is easier to query for reporting purposes. In other words create a MakeTable query that separates the Tx rows and the Vx rows and stores them in the table so that there is a column for the time, a column for the value, and an id column all in the same row.

HTH