Seperate Selection Criteria for each row? 1

[Delboy14]

(MS Access 2000)

I have a table of text strings, they are reccomendations. I am trying to write a query which will only contain the recommendations that are true. The problem I am having is each recommendation(cell) relates to different data from different tables.

Is there any way write a query which tests each record on its own formula and returns those that are true.
e.g.
Select GoodRec.Recommendation[1] // for recommendation1
FROM GoodRec,Value
WHERE x=1
UNION
Select GoodRec.Recommendation[2] // for recommendation1
FROM GoodRec,Score
WHERE y=3

this is just a guide for what I am trying to do.

 

[ProgrammerAndy]

Your example is confusing. The SQL statemets are not correct to begin with. Are you trying to get the recommendation from the table goodrec where your score or value equals 1 or 3??

if so your sql should read:

Select GoodRec.Recommendation[1] ' for recommendation1
FROM GoodRec where value = 1
UNION
Select GoodRec.Recommendation[2] ' for recommendation1
FROM GoodRec
WHERE Score = 3

Let me know if this is what you ar trying to do??

 

[Delboy14]

its alright thanks I have got it working

 

[dawnd3]

Delboy14, will you share with us what you did to make it work? Thanks

 

[Delboy14]

I added a feild which uniquely identified each row, this was a name for the row. I then tested each row by selecting the records that equaled the identifier and then applying that formula to the row. You can use UNION to join the queries on each row.

Select Recommendation
From Table, SmokingStatus
Where Identifier = 1 AND Smokers > 0;
Union
Select Recommendation
From Table, AlcoholStatus
Where Identifier = 2 AND AlocholConsumption > 0;

Using this you can apply seperate formulas for each row in your table