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
[Solved] Combine fields in report
[Solved] Combine fields in report
Last edited by rpriddle on Wed Sep 12, 2012 3:45 am, edited 1 time in total.
OOo 3.3 on Win7. <sigh>
Re: Combine fields in report
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"
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Combine fields in report
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Combine fields in report
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>
Re: [Solved] Combine fields in report
Try TRIM([first])&LEFT(" ";1)&TRIM([LAST])
Open Office 4.1.1 on Windows 8.1 / Android 4.3 / Windows 7