[Solved] Combine fields in report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Combine fields in report

Postby rpriddle » Mon Sep 10, 2012 3:44 pm

Trying to create a compact report in Base. 100 records with field names "first" "last" "phone" etc. 5 fields required for a report.

The database stores "first" and "last" separately, but I'd like to have a field in the report that lists "<first> <last>". Is this possible? And if it is, how do I do it?

(Oh, and if this has been answered earlier, I apologize - I couldn't find it.)
TIA

Roger Priddle
Last edited by rpriddle on Wed Sep 12, 2012 3:45 am, edited 1 time in total.
OOo 3.3 on Win7. <sigh>
rpriddle
 
Posts: 8
Joined: Mon Jan 09, 2012 4:11 am

Re: Combine fields in report

Postby Villeroy » Tue Sep 11, 2012 2:12 pm

Create a query with a concatenated field and use the query instead of the table.
SELECT "first" || ' ' || "last" AS "Full Name" , "other field" FROM "Some Table"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine fields in report

Postby Villeroy » Tue Sep 11, 2012 9:59 pm

The double-pipe || is the SQL concatenation operator.
Single quotes denote literal strings. So ' ' means a space.
Double-quotes denote object names (tables, fields) and their alias names.
From the table named "Some Table" we select the field named "first", concatenate with a space, concatenate with the field named "last", name the resulting field as "Full Name", select some "other field" too.
SQL is the lingua franca of databases.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combine fields in report

Postby rpriddle » Wed Sep 12, 2012 3:43 am

Thanks for this - it wasn't covered when I learned Fortran in 1972 <grin>. SQL is all new to me - I've had no real reason to learn it before - but I will trust that you've solved my problem. Many thanks.
OOo 3.3 on Win7. <sigh>
rpriddle
 
Posts: 8
Joined: Mon Jan 09, 2012 4:11 am

Re: [Solved] Combine fields in report

Postby plotkinms » Mon Apr 20, 2015 12:44 am

Try TRIM([first])&LEFT(" ";1)&TRIM([LAST])
Open Office 4.1.1 on Windows 8.1 / Android 4.3 / Windows 7
plotkinms
 
Posts: 1
Joined: Mon Apr 20, 2015 12:18 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest