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] Comparing one field in a record to another

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Alan Rose
Newbie
Newbie


Joined: 29 Jan 2009
Posts: 4

PostPosted: Fri Jan 30, 2009 7:06 pm    Post subject: [Solved] Comparing one field in a record to another Reply with quote

Beginner here, trying to make a first simple database.

I'm looking at the "Query" instructions in the Base help file and I can't figure out how to compare one field in a record to another field in the same record.

Say I want to identify all employees who live in the same city they were born in. Every employee has filled in a form including: "City born in" and "City of residence".

How do I call up every record in which these two fields have the same value?

Many thanks for the help.


Last edited by Alan Rose on Sat Jan 31, 2009 9:44 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Jan 31, 2009 4:39 am    Post subject: Reply with quote

Hello

I did try it and It looks that there is no easy way to it.
You can make the query in the GUI mode.
If you have made the query in the GUI mode
go to text mode in the query editor and add the next part

where "City born in" = "City of residence"


You will get something like this

Select * from "mytable" where "City born in" = "City of residence"
or

If you use this one then you have no problems with town and Town
Select * from "mytable" where upper("City born in") = upper("City of residence")

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


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Jan 31, 2009 9:25 am    Post subject: Reply with quote

Alan Rose:

Welcome to the OpenOffice Base.

You have asked, in my opinion, a very good question. And, using the GUI ( Graphical User Interface ) to create the Query, can be done, but, it has to be done with a "trick".
  1. Either, as Romke explained, ( Romke: thanks for your post above -- just want to say, thanks for all the help you give others on this forum )
  2. Or, as using the GUI as in the Graphics below, with a "trick"
Assuming I have a Table as described below ( yours may be different, just change it to match your names ). With the data described below, the ONLY instances of CITY_BORN_IN = CITY_OF_RESIDENCE are ( for ID of 0, 3, and 5 ):
  1. Toronto
  2. Edmonton
  3. Thunder Bay



Now, the "trick", I ADDED this Table to the Query TWICE, and
  1. LINK the PRIMARY KEY ( unique identifier of the table, in this case, it is named: ID )
  2. LINK the fields from the first table CITY_BORN_IN in first table to CITY_OF_RESIDENCE in the second table
This means . . . both, the ID must be the same and the cities must be the same . . . because of the LINK Smile

The LINK is assigned by using the mouse . . . LEFT CLICK on the field in question from the first table, and, while pressing the mouse, drag it to the appropriate field in the second table.



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
Alan Rose
Newbie
Newbie


Joined: 29 Jan 2009
Posts: 4

PostPosted: Sat Jan 31, 2009 9:41 am    Post subject: Reply with quote

I just came back here to thank Romke, whose tip worked and got me a long way toward answering all my related questions, and I see sliderule's response.

Haven't had a chance to look at that, yet, but I'll get to it after lunch.

Anyway: many, many thanks to both.
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Jan 31, 2009 10:54 am    Post subject: Reply with quote

Alan Rose:

After thinking about it . . . ( Sliderule thinking . . . now we are in trouble Laughing ) . . . there is another, much 'better / easier' way to do this from the GUI ( Graphical User Interface ) . . . sorry, I forgot about this 'technique.

The answer, use a pair of BRACKETS [ ] on the Criterion line. See the graphic below.



Likewise, if you wanted to 'see' where the CITY_BORN_IN is NOT the same ( rather than the same ), on the Criterion line, enter:

Code:
<> [CITY_OF_RESIDENCE]

Sliderule
Back to top
View user's profile Send private message
Alan Rose
Newbie
Newbie


Joined: 29 Jan 2009
Posts: 4

PostPosted: Sat Jan 31, 2009 12:00 pm    Post subject: Reply with quote

Excellent! I tried it both ways, with links and with brackets, and they both worked. As you say, the brackets are easier, but it was good to learn a little about linking tables.

Here's another one for you:

Let's say I want look at all my employees whose first, last, or middle name is James. That would be James Taylor, James Dean, and James Brown as well as Henry James, Jesse James, and LeBron James, plus any middle-name-Jameses.

(And of course, I'll want to be able to search for other names the same way.)

How do I write a parameter query that will ask me for a name and then search three different fields and return all records having that name in any of those fields?

Thanks again.

Alan Rose
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Jan 31, 2009 1:00 pm    Post subject: Reply with quote

Alan Rose:

You have now changed this forum subject, from Comparing one field in a record to another to questions about Parameter Queries . . . not related ( pun intended ) to your original post, and, will be hard for others to find answers, since the subject line now does not address this new question.

See the following links . . . for examples and explanations, including how to create a Parameter Query:
  1. http://www.oooforum.org/forum/viewtopic.phtml?t=60975
  2. http://www.oooforum.org/forum/viewtopic.phtml?t=74131
In your case, you would want to prompt the user with distinct parmeters for each part of a name, by entering the prompts below . . . using the a 'distinct' OR line for each.

Please note . . . the use of the colon ( : ) must immediately be followed by the words that are used in the prompt . . . AND . . . it MUST be one word ( no spaces ) AND start with an ALPHA character ( a-z not a number ). So, I like to use an underscore between words to help make it look, to the user, more readable. Also, the parameters are presented to the user in alphabetical order, as you have defined them after the colon.

Sliderule
Back to top
View user's profile Send private message
Alan Rose
Newbie
Newbie


Joined: 29 Jan 2009
Posts: 4

PostPosted: Sat Jan 31, 2009 11:50 pm    Post subject: Reply with quote

Sorry for changing the subject. I'll remember that in the future.

You information on parameter queries solved that problem as well. Thanks for the valuable help.

Alan Rose
Back to top
View user's profile Send private message Send e-mail
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