Combind fields for display

it is possible to concat the fields in the sql query page, see below> SELECT

`detentions`.`ID`,

`detentions`.`DateGiven`,

`detentions`.`detainee`,

`pupils`.`fname`,

`pupils`.`lname`,

`detentions`.`donor`,

`detentions`.`TutorGroup`,

`detentions`.`DayForDetention`,

`detentions`.`sess`,

`detentions`.`Lunchtime`,

`detentions`.`Done`,

`detentions`.`Origin`,

`detentions`.`MoveToDept`,

`detentions`.`DeptHead`,

`detentions`.`ReasonForDept`,

`detentions`.`OriginalDate`,

`reasons`.`reason` AS `Reason Given`,

`detentions`.`DoneAtDept`,

`detentions`.`MovedToAfterSchool`,

concat(`pupils`.`fname`,' ',`pupils`.`lname`) AS `Pupil`,

concat(left(`staff`.`firstname`,1),'. ',`staff`.`lastname`) AS `Teacher`,

concat(left(`v4dh`.`firstname`,1),'. ',`v4dh`.`lastname`) AS `Department Head`,

concat(left(`v4orig`.`firstname`,1),'. ',`v4orig`.`lastname`) AS `Originator`,

`detentions`.`depreset`,

`detentions`.`MoveToAfterSchool`,

`detentions`.`DateForAfterSchool`,

`detentions`.`DoneAfterSchool`,

`dept`.`department`,

`sessions`.`sess_time`

FROM

((((((`detentions`

Inner Join `pupils` ON (`pupils`.`idPupil` = `detentions`.`detainee`))

Inner Join `reasons` ON (`reasons`.`idReason` = `detentions`.`reason`))

Inner Join `staff` ON (`staff`.`userid` = `detentions`.`donor`))

Left Join `v4dh` ON (`v4dh`.`userid` = `detentions`.`DeptHead`))

Inner Join `dept` ON (`dept`.`idDept` = `detentions`.`department`))

Inner Join `sessions` ON (`sessions`.`sess_id` = `detentions`.`sess`))

Left Join `v4orig` ON `v4orig`.`userid` = `detentions`.`Origin`

I have found that you need to have a field from the main table, (detentions in my case) after any concat fields, or PHPR kicks up with wierd errors about incorrect syntax.

I use Navicat to build and test up my queries, then I paste them into PHPR.

If I used inner join for the 2 tables/views where I have used Left join, I only get the records where they have fields filled for the linked tables.

By using Left Join, I get all the records from the detentions table whether or not they have entries in the v4dh and v4orig tables.

HTH

I'm not sure this would work.

I am using two tables fixtures and people
In the fixtures table there are fields for vairous rotas I am using the people table for a look up list of people to be inserted into the fixture/rota fields.
In the people table I have seperate fields for surname and firstname so I have concantenated these in the lookup wizard but it will only insert surname in the fixture/rota fields.
I am trying to find a way to insert both surname and firstname.

I would not store the actual names, I assume you have given each record in the people table a unique number.

I always store the numbers in the main table, less work and much faster.

It also takes less space

so if Mr Sofa is record number 456, all you have to do is store 456 in the rota table.

When you come to print the rota, or show it on screen, use an inner join and then concat the firstname and lastname to give a man-readable display.

If you want to let me have the tables as a mysql dump, I will have a look and see what I can do?

Just put some dummy data in.

The alternative way to add the full name to the other table is to concatenate the fname and lname in the Before update event and then use the $values() to insert the value into the rota table.

It is a bit messy storing redundant data, cos if you have to change a name in the event of a marriage or something, you have all the records to change.

If you have just used an ID field, you can change the record for the ID and all is well.

Plus it takes a lot less storage and processing to store the ID numbers.

They all have unique numbers but I am not sure it is possible to edit tables with joins. This needs to be editable so I would opt for the alternative "Before update event" however I am not sure of the code to put in here?

They all have unique numbers but I am not sure it is possible to edit tables with joins. This needs to be editable so I would opt for the alternative "Before update event" however I am not sure of the code to put in here?

please post the table structures and i will try to help

People table has
`idpeople` PrimeKey

`FirstName`

`FamilyName `
+many other fields.
Fixtures Table has
`EventID` PrimeKey

`Event`

`EventType`

`Details`

`OOD`

`AOOD`

+many other fields.
I'm hoping to be able to combine FirstName and FamilyName via the lookup wizard and Insert/Edit the full name in the OOD field in the fixtures table.

Hi,
edit you SQL query on the Edit SQL query tab in the following way:

select `EventID`,

where field1, field2 are your actual field names.
Then uncheck OOD field on the ADD and EDIT pages on the Choose fields tab.