[Solved] Combining fields within queries

Creating tables and queries
Post Reply
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

[Solved] Combining fields within queries

Post by crawfy1 »

I have a table with two columns containing mobile phone numbers. One for contacts and then the other is for partners numbers. When entering data in a form, I want both number fields to search this combined query to check the numbers do not already exist in my database. I want to create a query that looks at both of these columns and returns all results in 1 column in numerical order. I can get one column to be displayed numerically or both in two separate columns but not joined into one column. Any suggestions.
Last edited by crawfy1 on Mon Jun 21, 2010 12:51 am, edited 1 time in total.
OpenOffice 3.2.0
Win 7
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Combining fields within queries

Post by Arineckaig »

I want to create a query that looks at both of these columns and returns all results in 1 column in numerical order.
Welcome to OOo Base and its challenges.

This is possible with a SQL UNION query, but the syntax tends to depend on the Database Engine that you are using. In any event the OOo Base GUI does not recognise UNION queries, but it is still possible to create the Query and run it DIRECTLY.

If you are using the embedded HSQL database, select "Create Query in SQL View..." from the queries window in Base. Type in the SQL commands listed below changing the field and table names as required, but before pressing f5 to run the SQL you must select the "Run SQL command directly" button in the toolbar [the icon has the letters SQL with a green check mark]. If this icon is not selected with will probably get an error. Hopefully if all goes well you can save the Query and it will always run the SQL command directly when double clicked in the Base window. If you are using another database engine the SQL syntax may differ. Let us know how it goes.

Code: Select all

SELECT "PartnersField" AS "ONE" FROM "TableName" 
UNION ALL
SELECT "ContactsField" AS "ONE" FROM "TableName" 
ORDER BY "ONE" ASC
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Re: Combining fields within queries

Post by crawfy1 »

Thanks for the assistance. Works a charm. Just one thing... some of the contacts do not have mobile phone numbers and so this is leaving a long list of empty spaces in the query. Is there any way i can remove the empty spaces or at least GROUP them to exclude duplicates???
OpenOffice 3.2.0
Win 7
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Combining fields within queries

Post by Arineckaig »

The simplest way is to delete the word "ALL". A simple UNION command excludes duplicates. This does mean, however, that any duplicate numbers already in the list will be shown only once.

The more thorough method would be to add a WHERE clause to each select command to exclude Nulls because lack of a number entry usually means a NULL. For example, try adding
WHERE "FieldName" IS NOT NULL
to each SELECT clause with its respective FieldName and keep the UNION ALL command. Your query result will thus still show any duplicated telephone and mobile numbers but hopefully no blanks.

When this issue has been resolved, it helps other users of the forum if you add [Solved] in your 1st post Subject (edit button top right).
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Re: Combining fields within queries

Post by crawfy1 »

Thanks again for all the help. Agin it works great.
OpenOffice 3.2.0
Win 7
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Combining fields within queries

Post by Arineckaig »

If this issue has been resolved, please remember to add the word [Solved] to the Subject line your 1st post by using the 'edit' button top right: you only have the right to edit your own posts.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Post Reply