OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

[Solved] Missing first record in query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
anon_private
General User
General User


Joined: 05 Mar 2009
Posts: 21
Location: UK
PostPosted: Fri May 06, 2011 6:42 am    Post subject: [Solved] Missing first record in query Reply with quote
Hi,

If I create a query with the wizard and set a condition such as greater than 5 and the field has a top value of say 20, I find that the values are listed except the 20.

How can I rectify this to reveal all the data?

Thanks

A

Last edited by anon_private on Fri May 06, 2011 9:04 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
PostPosted: Fri May 06, 2011 6:54 am    Post subject: Reply with quote
Problem: Your question is not clear to me, with what you want, nor what you did to create the Query. That is, exactly what the Query looks like. Smile

Solution:
  1. Open your Query in Edit mode . . . and . . . on the Toolbar is an icon: Switch Design View On/Off ( that is the icon with a gold triangle ) . . . click it.

  2. Copy and paste the generated SQL to this forum and perhaps we can help.

Sliderule
Back to top
View user's profile Send private message
anon_private
General User
General User


Joined: 05 Mar 2009
Posts: 21
Location: UK
PostPosted: Fri May 06, 2011 7:31 am    Post subject: Reply with quote
Thanks for responding and offering to help.

The pasted section is given below:

SELECT "ID" AS "ID", "Address" AS "Address", "City" AS "City", "CustomerID" AS "CustomerID", "FirstName" AS "FirstName", "LastName" AS "LastName", "Job Title" AS "Job Title", "Rank" AS "Rank", "Location" AS "Location", "Salary" AS "Salary", "Benefits" AS "Benefits" FROM "Customers" AS "Customers" WHERE "Salary" > '5'
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
PostPosted: Fri May 06, 2011 7:50 am    Post subject: Reply with quote
Below is the SQL exactly as you showed it above, EXCEPT, I added some line breaks to make it easier to read:

Code:
SELECT
   "ID" AS "ID",
   "Address" AS "Address",
   "City" AS "City",
   "CustomerID" AS "CustomerID",
   "FirstName" AS "FirstName",
   "LastName" AS "LastName",
   "Job Title" AS "Job Title",
   "Rank" AS "Rank",
   "Location" AS "Location",
   "Salary" AS "Salary",
   "Benefits" AS "Benefits"

FROM "Customers" AS "Customers"

WHERE "Salary" > '5'  -- You have 5 surrounded by single quotes, is this a TEXT field OR a NUMERIC field ?


OK, I do NOT have your database, but, the field "Salary" . . . in the WHERE says:

"Salary" > '5'

¿ ¿ ¿ is the field "Salary" defined as a TEXT field or a numeric ( number ) field ? ? ?

  1. You have 5 surrounded by single quotes ( '5' ), which means, it a TEXT field, and, I wonder if it really should be a number, that is withOUT the single quotes.

  2. Another possibility is, you have NOT scrolled down to the BOTTOM ( the end ) of the result set, and, the data is really there, but, you have NOT scrolled to see ALL THE DATA PRESENT. Smile

Solution:

Go the the same screen ( after pressing the Switch Design View On/Off icon ) you copied the SQL from , and, replace it with the SQL below, and, Run it ( F5 key, or, Run Query icon )
Code:
SELECT
   "ID" AS "ID",
   "Address" AS "Address",
   "City" AS "City",
   "CustomerID" AS "CustomerID",
   "FirstName" AS "FirstName",
   "LastName" AS "LastName",
   "Job Title" AS "Job Title",
   "Rank" AS "Rank",
   "Location" AS "Location",
   "Salary" AS "Salary",
   "Benefits" AS "Benefits"

FROM "Customers" AS "Customers"

WHERE "Salary" > 5  -- Changed by Sliderule, 5 withOUT single quotes

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary
PostPosted: Fri May 06, 2011 8:08 am    Post subject: Reply with quote
What type of database you connected?

The problems happens in OOo 3.3 with certain connections to mdb files and MS SQL server. ADO connections fails, but ODBC works.

See: http://openoffice.org/bugzilla/show_bug.cgi?id=116509
Back to top
View user's profile Send private message
anon_private
General User
General User


Joined: 05 Mar 2009
Posts: 21
Location: UK
PostPosted: Fri May 06, 2011 8:48 am    Post subject: Reply with quote
Thanks for replying

I pasted your code and got the same query, ie, it lacked the highest number.

However, on viewing my data, I noticed that the salary field was given as text. On changing this to integer, I got all the expected numbers in the query.

I am not sure why your code did not work!

As a matter of interest, can databases that have say eight fields and only four records be posted here for comments?

Thanks.

A
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
PostPosted: Fri May 06, 2011 8:58 am    Post subject: Reply with quote
You said / asked:

Quote:
However, on viewing my data, I noticed that the salary field was given as text. On changing this to integer, I got all the expected numbers in the query.

Since, the issue was your database design ( defining a field with the wrong field type ), that is why you had the issue.

I would, as you have done, changed the field type to an integer.

The Query, IF IT WAS STILL A TEXT FIELD, could have been written ( NOT recommended ) as:

Code:
SELECT
   "ID" AS "ID",
   "Address" AS "Address",
   "City" AS "City",
   "CustomerID" AS "CustomerID",
   "FirstName" AS "FirstName",
   "LastName" AS "LastName",
   "Job Title" AS "Job Title",
   "Rank" AS "Rank",
   "Location" AS "Location",
   "Salary" AS "Salary",
   "Benefits" AS "Benefits"

FROM "Customers" AS "Customers"

WHERE CAST("Salary" as INTEGER) > 5   -- Make field an INTEGER for comparison only

No, this forum does NOT allow 'embedding' files. Images yes, files no.

You can, if you want, UPLOAD the file to a free service, such as:

http://www.mediafire.com/

and provide the link URL so others can download the file.

PLEASE: mark this as [Solved] per the instructions below.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
anon_private
General User
General User


Joined: 05 Mar 2009
Posts: 21
Location: UK
PostPosted: Fri May 06, 2011 6:30 pm    Post subject: Reply with quote
Just a note to thank you for the help.

I have learnt something.

Best wishes.

A
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group