for next with edit records

[JJCHCK]

Hi everybody
I have a table</SPAN> with 2 fields: product and state.
I would write 1 only where it appears in fields “state” for the same item
I woult get B,D,E,G,H in my query !!!!
what's wrong in this code?



HELP

[Robeen]

It's difficult to understand what you need.
Can you explain in different words?

[JJCHCK]

Id like to edit in fields "ConataSoloUno" only that items that have in every record the value 1 so:
A has not to compare while B yes....like in atthachment!

I hope I cleared ...sorry for english

[Robeen]

Huh???

[JJCHCK]

i forgot the attachments

[Robeen]

Is your problem with Query 'Q_risultato'?

If so, this is the SQL you have [View -> SQL View]:

 
SELECT DISTINCT Tabella2.articolo
FROM Tabella2
WHERE Tabella2.ContaSoloUno = '1';

The field 'ContaSoloUno' in Tabella2 does not have any '1'.
So you will not get any rows from your query.

The field 'stato' has 22 rows with '1' in them.

If you change your SQL to:

 
SELECT DISTINCT Tabella2.articolo
FROM Tabella2
WHERE Tabella2.stato = '1';

. . . you will get all rows that have a 1 in them [including A, C, F . . .].

If you want only 'articolo' that do not have any '0' then you can create a query and put this SQL in there:

 
SELECT distinct Tabella2.articolo, Tabella2.stato
FROM Tabella2
WHERE Tabella2.articolo not in
(SELECT distinct Tabella2.articolo from Tabella2 where Tabella2.stato = '0') ;

OR

Open Q_risultato
Click View -> SQL View
Paste this in there:

 
SELECT distinct Tabella2.articolo, Tabella2.stato
FROM Tabella2
WHERE Tabella2.articolo not in
(SELECT distinct Tabella2.articolo from Tabella2 where Tabella2.stato = '0') ;

I hope this helps!!

Mark it solved if your problem is fixed.

[JJCHCK]

thanks for answer but i know that rst.fields(ContaSoloUno) is empty...i where i would write something like "x" or "1" or somethingelse...
the problem is not in my query: just only to test the result of my routine in button("for next 1")!
My routine seems works but seems olso not evaluate the first record of all product
My select distinc of product (A,B,C...) is right when it shows only B,D,E,G,H !

please help me

[JJCHCK]

please look know "tabella2"

[Robeen]

I think I understand what you are saying.

Why do you need a 1 in 'ContaSoloUno'?
What is the purpose for that?

[JJCHCK]

the aim is to identify those products (A,B,C....) to eliminate but every one
has several variants....to simplify i don't insert another fields with those variants....
when one product has all variants to 1 i have to select and report to my Systems Engineer wiht a view to eliminate

[Robeen]

I'm not sure if this helps, but you can try it.
It gives you [I think] what you need.

 
SELECT Tabella2.articolo, Tabella2.stato
FROM Tabella2
WHERE Tabella2.articolo not in
(SELECT distinct Tabella2.articolo from Tabella2 where Tabella2.stato = '0') ;

I don't know if you can use it for what you need . . .

[JJCHCK]

greate...this seems the solutione....i use this
but at the same time i would use the code...i'm so near the solution:
my code jump one record for every product....

[Robeen]

I was looking at your code in the 'for next 1' button.

There is something that may be a problem with your MoveFirst logic . . .
I will take a loook but it may be a little later as i have work to do . . .

I will let you know if I figure out why your code is skipping the first one of every articolo . . .

[JJCHCK]

Thanks a lot...
...

[JJCHCK]

i can solvewith a trick: adding a record of field to zero (eg: field 1 = null and field 2 = 0)
But it is not elegant ...or professional

what can i do ?