[Solved] Comparing one field in a record to another

Alan Rose · Newbie Joined: 29 Jan 2009 Posts: 4

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.

RPG · Posted: Sat Jan 31, 2009 4:39 am Post subject:

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

Sliderule · Posted: Sat Jan 31, 2009 9:25 am Post subject:

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".

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 )
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 ):

Toronto
Edmonton
Thunder Bay

Now, the "trick", I ADDED this Table to the Query TWICE, and

LINK the PRIMARY KEY ( unique identifier of the table, in this case, it is named: ID )
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.

Alan Rose · Newbie Joined: 29 Jan 2009 Posts: 4

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.

Sliderule · Posted: Sat Jan 31, 2009 10:54 am Post subject:

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:

Alan Rose · Newbie Joined: 29 Jan 2009 Posts: 4

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

Sliderule · Posted: Sat Jan 31, 2009 1:00 pm Post subject:

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:

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

Alan Rose · Newbie Joined: 29 Jan 2009 Posts: 4

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