Conditions in selection query (1 Viewer)

[Jacino]

Hi every body,

I am new in this discussion form.
In fact, I have the following problem:
I have a selection query of three tables, in which I select the data with certain conditions. It is imperatif that a filed of one of the tables be equal to another one (under certain conditions!).

At the criteria line of this field I have generated the tha following condition:

Iif(RSKS97!NPO3>0 , RSKS97!NPO3,
Iif(RSKS97!NPO2>0 , RSKS97!NPO2,
Iif(RSKS97!NPO1>0 , RSKS97!NPO1,
Iif(RSKS02!NPOR>0 ,[RSKS02!NPOR,))))

That means, it checks three fields in the second table. If they are zeros, then it will be equal to a field in the third table.

But unfortuntly, it doesn't work. An error message indicates that the syntax of the expression is not correct.
Have you any suggestion. It would be really appreicitated.

 

[David R]

At the risk of being obvious, you have a stray [ and a stray , near the end of your example. Was that a typo here or is that in your query as well?

Although your field names aren't very descriptive, I would say your data is probably not normalized. Anytime sequences of numbers become part of your field names (NP01, NP02, etc), you are probably using repeating groups, which violates First Normal Form. Look up "database normalization" and see if that helps.

[This message has been edited by David R (edited 05-30-2002).]

 

[Jacino]

I am very sorry, this is the correction:

Iif(RSKS97.NPCO3>0 , RSKS97.NPCO3,
Iif(RSKS97.NPCO2>0 ,RSKS97.NPCO2,
Iif(RSKS97.NPCO1>0 ,RSKS97.NPCO1,
Iif(RSKS02.NPCOR>0 ,RSKS02.NPCOR,0)))))

No problem for the normalization. As a matter of fact I needed to list in one query certain records which meet the requirements.
Could you go furthur please in helping me finding the solution. Thanks alot.

 

[David R]

Can you explain in plain English what you are trying to do again? I'm not sure I follow...

The IF statement as set out checks NPCO3, NPCO2, NPCO1, NPCOR (from a different table?) in order. If they have any value other than 0, it shows that value. If they ALL are equal to zero, then the value is 0.

You mentioned 3 tables but I only see 2. And checking three fields in one table against 1 in another again leads me to believe you have repeating groups. What does "NPCO#" stand for, if I can ask?

 

[Jacino]

Pardon for my English. The situation is as what you have explained:
The query is consisting of three tables RSKS02, RSKS97 and RSKS19.
In the table RSKS02 we are concerned by the field NPCOR.
In the table RSK97 we do concern by three fields NPCO1, NPCO2 and NPCO3.
For the table RSKS19 we need to query the records that meet the mentioned criteria for the field [RSKS19].[NXAS].
That means:
if NPC03>0 then NXAS = NPCO3 else we do this loop of searching the value.

An answer to your question:

The IF statement as set out checks NPCO3, NPCO2, NPCO1, NPCOR (from a different table?)in order (YES!) . If they have any value other than 0, it shows that value. If they ALL are equal to zero, then the value is 0.

In SQL language without the condition:

SELECT RSKS19.NXAS, RSKS19.CAX
FROM RSKS02, RSKS97, RSKS19
WHERE (NXAS=NPCO3 or NPCO2 or NPCO1 or NPCOR) (one of them have to be non zero.)

Thanks once again.

 

[The_Doc_Man]

Part of the problem is that Access has TOO MUCH data to consider here - or at least that is what I see from this description.

You are asking Access to find cases where a field in one table is equal to a field in any one of three other tables. In other words, if value XYZ from table W also appears in a particular field in any one of tables A, B, or C, you want some other data values from that record in table W.

I am going to simplify your answer because I hate typing. Let us say that field "NCP" appears in each of tables W, A, B, and C. Table W is the one that contains the info you really want but ONLY if you have matching data in field NCP of A, B, or C.

Look at this function:

DCount("[NCP]", "A", "[A].[NCP]=""" & [W].[NCP] & """")

(This assumes that NCP is text. If it is not, you could simplify it to

DCount("[NCP]", "A", "[A].[NCP]=" & CStr([W].[NCP] )

You could write similar expressions for tables B and C, of course...

Now write a query based on W that includes the NCP field and the other fields you wanted from W, plus three DCount expressions in the field name boxes for each of three columns, one such expression for each of tables A, B, and C.

In the criteria rows, this part gets critical. Under the expression for A, set a criterion of ">0" in the first criterion row. Under the expression for B, set the same criterion but in the SECOND criterion row. Under the expression for C, set the same criterion but in the THIRD criterion row.

In English, this query says "find values in the W table for which at least one of the counts of matched entries in the other tables is not zero."

In order to help Access make this discovery for you, it might be a good idea to place a non-unique index on each of the [NCP] fields of tables A, B, and C. Believe it or not, it does not matter whether [NCP] is indexed in table W. (It isn't the one being counted.)

Hope this makes enough sense for you to see what I am suggesting.

 

[Jacino]

Thanks alot.