Source language: Translate to:

Mostrar solo campos llenos y sin repeticion

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Thu Dec 18, 2014 8:55 pm

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
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby stu » Fri Dec 19, 2014 11:04 am

Syntax: [ Download ] [ Hide ]
Using SQL Syntax Highlighting
SELECT DISTINCT * FROM PROVEEDORES WHERE (name IS NOT NULL);
Parsed in 0.005 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


>=)
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Mostrar solo campos llenos y sin repeticion

Postby Neosoft Support » Fri Dec 19, 2014 12:07 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Fri Dec 19, 2014 5:19 pm

dbpQuery "datos" "Tabla1" "SELECT DISTINCT * FROM PROVEEDOR WHERE [value] IS NOT NULL"
SINTAXIS ERROR - HELP
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby stu » Fri Dec 19, 2014 6:50 pm

My example works fine on MS SQL Server 2005, try quoting, should work. ;)
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Sat Dec 20, 2014 9:49 am

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"
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby Gaev » Sat Dec 20, 2014 10:34 am

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
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Mon Dec 22, 2014 9:18 am

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
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby Gaev » Mon Dec 22, 2014 10:35 am

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 ?
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Mostrar solo campos llenos y sin repeticion

Postby dpayer » Mon Dec 22, 2014 12:57 pm

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]));
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Mon Dec 22, 2014 4:27 pm

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
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Tue Dec 23, 2014 5:13 pm

neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby Gaev » Tue Dec 23, 2014 10:33 pm

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
Last edited by Gaev on Wed Dec 24, 2014 6:10 am, edited 1 time in total.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Mostrar solo campos llenos y sin repeticion

Postby neobookruletin » Tue Dec 23, 2014 11:08 pm

SYNTAX ERROR
dbpExecSQL "datos" "!SELECT DISTINCT PROVEEDOR FROM Tabla1 WHERE (PROVEEDOR IS LIKE '[value]%);" ""

http://postimg.org/image/dgdbiy03n/
neobookruletin
 
Posts: 31
Joined: Tue Nov 25, 2014 9:17 am

Re: Mostrar solo campos llenos y sin repeticion

Postby Gaev » Wed Dec 24, 2014 6:17 am

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.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests