Evaluate query

[Guest]

Hi,

I want a field (TrendOverTime) in a table to have a string inserted; UP, DOWN or OK, based on the result of query1.

Query1 contains 0-5 rows and some fields/columns, but the only field of interest here is Trend. If query1 contains 0-5 rows where _every_ row's Trend-field states "UP" or "DOWN", I want the TrendOverTime-field in the table to state "UP" respectively "DOWN" as well. If the query1 Trend-fields would be containing any other combinations or no rows at all, the table field should be set "OK".

The questions: how do I ask a table field to execute and request the results of query1 and evaluate those as described above? Is it possible or should I take another approach?

Many thanks, Peter

 

[Michel Walsh]

Hi,


Your specifications are somewhat a little bit fuzzy. Maybe something like
(Jet only) :


SELECT *,
SWITCH( NOT EXISTS( SELECT * FROM query1) , 'OK',
'UP' = ALL (SELECT trend FROM query1) , 'UP',
'DOWN' = ALL (SELECT trend FROM query1), 'DOWN',
True, 'OK') As unanimousTrend

FROM somewhere




The expression:

constant = ALL (SELECT fieldName FROM tableName)


return TRUE if all the records in tableName have their field fieldName value
equal to the supplied constant; or if there is NO record in the tableName,
else, if there is at least one record and that one of the record has a value
different than the constant, the expression return false.

So, I first check if there is 0 record, if so, return OK, if not, check if
all records are UP, or all are DOWN, and finally, in other cases, I just
return "OK".

Someone may have use, instead of 'up' = ALL (SELECT trend FROM query1)
something like: 0=(SELECT COUNT(*) FROM query1 WHERE trend <> 'up' )

and 0 = (SELECT COUNT(*) FROM query1)
instead of NOT EXISTS(SELECT * FROM query1)



Hoping it may help,
Vanderghast, Access MVP

Hi,

I want a field (TrendOverTime) in a table to have a string inserted; UP,

DOWN or OK, based on the result of query1.

Query1 contains 0-5 rows and some fields/columns, but the only field of

interest here is Trend. If query1 contains 0-5 rows where _every_ row's
Trend-field states "UP" or "DOWN", I want the TrendOverTime-field in the
table to state "UP" respectively "DOWN" as well. If the query1 Trend-fields
would be containing any other combinations or no rows at all, the table
field should be set "OK".

The questions: how do I ask a table field to execute and request the

results of query1 and evaluate those as described above? Is it possible or
should I take another approach?

 

[Guest]

Thanks for helping out, I'll try to be more specific.

I run MS Access 2000, and are working in a .mdb-file. (Is Jet applicable then?)

Table1 looks like: "RefID", "RefNo", "TrendOverTime"
Query1 looks like: "Date", "PreviousDate", "Trend" where Trend defines the trend (UP/DOWN/NEITHER) since the previous date.

I have another table (let's call it Table2) with data, related to Table1's "RefID", from which Query1 get it's information.

What I want to accomplish is that when I create a new record of information in Table2, I want the field "TrendOverTime" in Table1 to uppdate itself by evalutating the information in query1. For example: If all Trend fields in query1 says "UP", then Table1.TrendOverTime should be "UP". And if the Trend fields are not all either UP or DOWN, or if there are no records, Table1.TrendOverTime should say OK.

Please have indulgence with language errors,
Peter

 

[Michel Walsh]

Hi,

A dot-mdb file is indeed supported by Jet, by opposition to a adp supported
by MS SQL Server.

I still miss the relation between Query1 and table1... I suspect, expect,
there should be a field, in query1, that supplies a "tie" with the
table1.RefID field. I have use ??? here down for that field name:


SELECT Table1.*,
SWITCH( NOT EXISTS( SELECT * FROM query1 WHERE query1.??? =
table1.RefID) , 'OK',
'UP' = ALL (SELECT trend FROM query1 WHERE query1.??? =
table1.RefID) , 'UP',
'DOWN' = ALL (SELECT trend FROM query1 WHERE query1.??? =
table1.RefID), 'DOWN',
True, 'OK') As unanimousTrend

FROM table1



Those results should then be append to table2 (or something similar).




Hoping it may help,
Vanderghast, Access MVP

Thanks for helping out, I'll try to be more specific.

I run MS Access 2000, and are working in a .mdb-file. (Is Jet applicable then?)

Table1 looks like: "RefID", "RefNo", "TrendOverTime"
Query1 looks like: "Date", "PreviousDate", "Trend" where Trend defines the

trend (UP/DOWN/NEITHER) since the previous date.

I have another table (let's call it Table2) with data, related to Table1's

"RefID", from which Query1 get it's information.

What I want to accomplish is that when I create a new record of

information in Table2, I want the field "TrendOverTime" in Table1 to uppdate
itself by evalutating the information in query1. For example: If all Trend
fields in query1 says "UP", then Table1.TrendOverTime should be "UP". And if
the Trend fields are not all either UP or DOWN, or if there are no records,
Table1.TrendOverTime should say OK.