Page 1 of 2

Mostrar solo campos llenos y sin repeticion

PostPosted: Thu Dec 18, 2014 8:55 pm
by neobookruletin
Tengo una columna llamada PROVEEDORES en la cual tengo muchos nombres iguales ya que varios articulos tienen el mismo proveedor y otros articulos que no tienen proveedores.
La pregunta es la siguiente:
Cuando consulto los PROVEEDORES necesito que me muestro solo el nombre de cada uno y que no se vean los campos vacios. Asi
Venier
3M

Y no asi como me sale:
Venier
Venir

3M
3M

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Fri Dec 19, 2014 11:04 am
by stu
Syntax: [ Download ] [ Hide ]
Using SQL Syntax Highlighting
SELECT DISTINCT * FROM PROVEEDORES WHERE (name IS NOT NULL);
Parsed in 0.007 seconds, using GeSHi 1.0.8.10


if you have blank fields you should query:

Syntax: [ Download ] [ Hide ]
Using SQL Syntax Highlighting
SELECT DISTINCT * FROM PROVEEDORES WHERE (name IS NOT NULL) AND (name <> '');
Parsed in 0.005 seconds, using GeSHi 1.0.8.10


>=)

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Fri Dec 19, 2014 12:07 pm
by Neosoft Support
We do have a Spanish NeoBook section on the forum where you can post questions in Spanish. If you post in the other English sections of the forum, please include an English translation so that other users can participate. You can use Google Translate to convert your question to English: https://translate.google.com

Thank you.

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Fri Dec 19, 2014 5:19 pm
by neobookruletin
dbpQuery "datos" "Tabla1" "SELECT DISTINCT * FROM PROVEEDOR WHERE [value] IS NOT NULL"
SINTAXIS ERROR - HELP

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Fri Dec 19, 2014 6:50 pm
by stu
My example works fine on MS SQL Server 2005, try quoting, should work. ;)

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Sat Dec 20, 2014 9:49 am
by neobookruletin
dbpQuery "datos" "Tabla1" "SELECT DISTINCT * FROM PROVEEDOR WHERE [value] IS NOT NULL"
SINTAXIS ERROR - HELP

It's A DATABASE ACCESS, TU SYNTAX NOT WORK, STRIP ERROR
dbpOpenAccessDatabase "datos" "C:\ver\Database11.mdb"

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Sat Dec 20, 2014 10:34 am
by Gaev
neobookruletin:

stu suggested ...
Code: Select all
SELECT DISTINCT * FROM PROVEEDORES WHERE (name IS NOT NULL);

... this is meant to be used with the dbpExecSQL command e.g. ...
Code: Select all
dbpExecSQL "datos" "SELECT DISTINCT * FROM yourTableNameHere WHERE (yourFieldNameHere IS NOT NULL);" ""

... it looks like the name of your ...

- Table is Tabla1
- Field is PROVEEDOR

... so try ...
Code: Select all
dbpExecSQL "datos" "SELECT DISTINCT * FROM Tabla1 WHERE (PROVEEDORE IS NOT NULL);" ""

... note that in your first post, you call the field PROVEEDORES ... if so, change PROVEEDORE above to PROVEEDORES !!!

It's A DATABASE ACCESS, TU SYNTAX NOT WORK, STRIP ERROR
dbpOpenAccessDatabase "datos" "C:\ver\Database11.mdb"

... if you are getting an error (SINTAXIS ERROR - HELP) when running this command, try ...
Code: Select all
dbpOpenAccessDatabase "datos" "!C:\ver\Database11.mdb"

... notice the ! before the c:\ver\Database11.mdb

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Mon Dec 22, 2014 9:18 am
by neobookruletin
dbpExecSQL "datos" "SELECT DISTINCT * FROM Tabla1 WHERE (PROVEEDOR IS NOT NULL);" ""
blank fields disappear, but still appears so ...
franco fia
franco fia
franco fia
venier
venier
venier

I need the result of the search is well
franco fia
venier

and within the syntax where I put the variable text entry button that is looking

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Mon Dec 22, 2014 10:35 am
by Gaev
neobookruletin:

blank fields disappear, but still appears so ...
franco fia
franco fia
franco fia
venier
venier
venier

I need the result of the search is well
franco fia
venier

Try ...
Code: Select all
dbpExecSQL "datos" "SELECT DISTINCT PROVEEDOR FROM Tabla1 WHERE (PROVEEDOR IS NOT NULL);" ""


and within the syntax where I put the variable text entry button that is looking


The translation of your first post was ...
I have a column called SUPPLIERS in which I have many of the same names as several articles have the same vendor and other items that do not have providers .
The question is:
When consulted SUPPLIERS need to show me just the name of each and empty fields are not visible. Asi
Venier
3M

And there also I get:
Venier
Venir

3M
3M


So I do not understand your latest question ... please explain in detail what you are trying to do ... and what is a text entry button ?

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Mon Dec 22, 2014 12:57 pm
by dpayer
I found this example of using SELECT DISTINCT in Access

http://stackoverflow.com/questions/5788 ... -ms-access


I think you have to give the specific field you want to be distinct. You are using * and that implies you want to choose all records in the table so if you want distinction, you need to tell it which field you want to be distinct.

Their example is in this format:
Code: Select all
SELECT DISTINCT Task.Priority, Task.Subject, Task.Status, Task.DueDate,
Task.Completed, Categories.Category
FROM Task, Categories
WHERE (((Categories.CategoryID)=[Task].[CategoryID]));

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Mon Dec 22, 2014 4:27 pm
by neobookruletin
DAVIS THANKS FOR ANSWER, YOU EXPLAIN THE PROBLEM.
HAVE A DATABASE IN A COLUMN NO ITEMS AND OTHER COLUMN SUPPLIER.
EACH ITEM HAS ITS SUPPLIER EJ:
PRODUCTO - PROVEEDOR
PINCEL - VENIER
RODILLO - VENIER
SERUCHO - 3M
SEARCH FOR ITEMS YOU ACHIEVE
dbpQuery "datos" "Tabla1" "PRODUCTO LIKE [#34]%[value]%[#34]" WORKS OK.

BUT TO SEARCH PROVEDOOR, NOT WORK.
AS SHOW ME THE SAME SUPPLIER SEVERAL TIMES. EJ: TEXT ENTRY VENI (SERIOUS THE VARIABLE
[value])
EN LA TABLA SALE ASI:
VENIER
VENIER


AND I NEED TO LEAVE ONE SUPPLIER
VENIER

NEED TO SHOW ONE SUPPLIER NOT TO SHOW ALL

WOULD SEND A PICTURE
BUT NOT AS IS MADE

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Tue Dec 23, 2014 5:13 pm
by neobookruletin

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Tue Dec 23, 2014 10:33 pm
by Gaev
neobookruletin:

With the dbpExecSQL command suggested in my last post ...
Code: Select all
dbpExecSQL "datos" "SELECT DISTINCT PROVEEDOR FROM Tabla1 WHERE (PROVEEDOR IS NOT NULL);" ""
... you should have a list of PROVEEDOR values that are DISTINCT (i.e. no duplicates).

If you need to pare it down to match values entered by the user in a TextEntry Box (whose variable is stored in [TextEntry1] ), try ...
Code: Select all
dbpExecSQL "datos" "!SELECT DISTINCT PROVEEDOR FROM Tabla1 WHERE (PROVEEDOR IS LIKE '[TextEntry1]%');" ""


For a better understanding of SQL, check out this page ... and then, on this page, try out ...


1) SELECT City FROM Customers;

You should get Number of Records: 91 ... including duplicates.

2) SELECT DISTINCT City FROM Customers;

You should get Number of Records: 69 ... no duplicates.

3) SELECT DISTINCT City FROM Customers WHERE City LIKE 'L%';

You should get ... Number of Records: 7 ... and these record values ...
Lander
Leipzig
Lille
Lisboa
London
LuleƄ
Lyon

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Tue Dec 23, 2014 11:08 pm
by neobookruletin
SYNTAX ERROR
dbpExecSQL "datos" "!SELECT DISTINCT PROVEEDOR FROM Tabla1 WHERE (PROVEEDOR IS LIKE '[value]%);" ""

http://postimg.org/image/dgdbiy03n/

Re: Mostrar solo campos llenos y sin repeticion

PostPosted: Wed Dec 24, 2014 6:17 am
by Gaev
neobookruletin:

You got a SYNTAX ERROR because there was a missing single quote ( ' ) in my previous post ... I have fixed it ... try it again with the fixed syntax.

If you are going to be developing Applications that deploy such advanced Database access techniques, you should first take some time to learn about SQL (capabilities, commands, syntax) ... so in future, you can figure out such simple typos all by yourself.