[Solved] Sorting on source info instead of reference

[dreamquartz]

Hi All,

Need to be able to sort on source info, while entering date in a reference table.
Have 2 tables.
1. tStaff
2. tPerson

tPerson is the source for tStaff, and the selected person from tPerson is entered via a Foreign Key.

Using a form for tPerson, and sub-form for tStaff, I can only sort on the Foreign Key but not on the Surname or GivenName of the referenced person.

I can create a fill out form and a reference form, but would like to keep it in one place, and use one form.

Is there a way to make the information from the source table tPerson available to tStaff for that purpose?

Dream

[Villeroy]

http://www.hsqldb.org/doc/1.8/guide/ch0 ... ct-section

SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ...
ORDER BY orderExpression

orderExpression
{ columnNr | columnAlias | selectExpression }
[ASC | DESC]


SELECT this, that FROM somewhere
ORDER BY (SELECT elsewhere.Name FROM elsewhere WHERE somewhere.EID = elsewhere.ID)

[dreamquartz]

http://www.hsqldb.org/doc/1.8/guide/ch0 ... ct-section

SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ...
ORDER BY orderExpression

orderExpression
{ columnNr | columnAlias | selectExpression }
[ASC | DESC]


SELECT this, that FROM somewhere
ORDER BY (SELECT elsewhere.Name FROM elsewhere WHERE somewhere.EID = elsewhere.ID)

I thank you for this answer, but it is not quite what I am looking for.

What I am looking for is the following principle (see picture).

I do not know if something like this can be in a combination with both input and reference form format.
I can be done seperately, in 2 forms, but combined is just a little nicer.

[Villeroy]

Select the table you want to edit, the PK of the detail table "tStaff" and the concatenated field. This might give an editable record set because you include the other table's PK.

Code: Select all

SELECT "P".*,
  "S".StaffNumber",
  "P"."Surname"||', '||"P"."GivenName" AS "Staff" 
FROM "tPerson" AS "P" JOIN "tStaff" AS "S" 
ORDER BY "Staff" ASC

Oh, and you can SELECT * FROM "tPersons" ORDER BY (SELECT ... "tStaff")ASC and substitute the foreign key with a list box showing the concatenated name for the staff ID.

[dreamquartz]

Select the table you want to edit, the PK of the detail table "tStaff" and the concatenated field. This might give an editable record set because you include the other table's PK.

Code: Select all

SELECT "P".*,
  "S"."StaffNumber",
  "P"."Surname"||', '||"P"."GivenName" AS "Staff" 
FROM "tPerson" AS "P" JOIN "tStaff" AS "S" 
ORDER BY "Staff" ASC

Oh, and you can SELECT * FROM "tPersons" ORDER BY (SELECT ... "tStaff")ASC and substitute the foreign key with a list box showing the concatenated name for the staff ID.

Sorry,

Don't work.

Syntax only provides errors.

I think you meant something like this:

Code: Select all

SELECT "P".*,
      "S"."StaffNumber",
      "P"."Surname"||', '||"P"."GivenName" AS "Staff"
    FROM "tPerson" AS "P" JOIN "tStaff" AS "S" ON "S"."FKPersonID" = "P"."PersonID"
    ORDER BY "Staff" ASC

However, it still does not provide what I am looking for.

I would like to add/update/remove the Staff Member in "tStaff"."Staff", but also sort on the same records.
The problem I think, lays in the fact that tStaff references tPerson, for the PersonID.
I tried to create an SQL-statement for the tPerson- and also for the tStaff-form, but that only leads to be able to list, and not manipulate data.

I am at a loss to create a nice form.

Dream

[Arineckaig]

I would like to add/update/remove the Staff Member in "tStaff"."Staff", but also sort on the same records.
The problem I think, lays in the fact that tStaff references tPerson, for the PersonID.

PROVIDED there is nothing more complicated than a One-to-Many relationship between the 'tPerson' and 'tStaff' tables, the Base GUI will effectively display a record-set sourced from a Join query.

PROVIDED that the Join query includes the primary key fields from all the source tables, the Base GUI should generally permit data from any table to be UPDATED from a single data form.

The Base GUI should also permit a single data form to delete any record displayed from a 'One-to-Many' Join record-set: such deletions will remove the relevant record from only the many-side (tStaff) table, but will generally leave the one-side (tPerson) table unchanged.

Similarly the Base GUI can permit that data form to add new records to the many-side (tStaff) table: care is required however to avoid potential 'orphans' by ensuring the value entered in the Foreign Key field correctly references an existing record in the one-side (tPerson) table. A single data form sourced from a Join query record-set can confuse users and is seldom well suited for entry of new records: for this purpose a parent and a linked sub-form tends to prevent orphans and to be less prone to user error.

It should be equally possible to use any field or combination of fields to sort a record-set sourced from a Join query.

Without a sample of your Base file (with all sensitive data removed) it is difficult to assess the cause of the problem. If preferable I could prepare and offer a crude demo, but such a file is less likely to meet your specific requirement.

[dreamquartz]

I would like to add/update/remove the Staff Member in "tStaff"."Staff", but also sort on the same records.
The problem I think, lays in the fact that tStaff references tPerson, for the PersonID.

PROVIDED there is nothing more complicated than a One-to-Many relationship between the 'tPerson' and 'tStaff' tables, the Base GUI will effectively display a record-set sourced from a Join query.

PROVIDED that the Join query includes the primary key fields from all the source tables, the Base GUI should generally permit data from any table to be UPDATED from a single data form.

The Base GUI should also permit a single data form to delete any record displayed from a 'One-to-Many' Join record-set: such deletions will remove the relevant record from only the many-side (tStaff) table, but will generally leave the one-side (tPerson) table unchanged.

Similarly the Base GUI can permit that data form to add new records to the many-side (tStaff) table: care is required however to avoid potential 'orphans' by ensuring the value entered in the Foreign Key field correctly references an existing record in the one-side (tPerson) table. A single data form sourced from a Join query record-set can confuse users and is seldom well suited for entry of new records: for this purpose a parent and a linked sub-form tends to prevent orphans and to be less prone to user error.

It should be equally possible to use any field or combination of fields to sort a record-set sourced from a Join query.

Without a sample of your Base file (with all sensitive data removed) it is difficult to assess the cause of the problem. If preferable I could prepare and offer a crude demo, but such a file is less likely to meet your specific requirement.

I have never found a satisfying answer to this very basic problem.

[Villeroy]

Your database is not readable. It is an embedded HSQL2 database with passwod protection. If you set the hsql2 driver in the global office options, you can not use embedded HSQLDB anymore. I'm fine with HSQL2 but then there should be no protected user account.

[chrisb]

Edit: 18-Jan-17 02:06 have added attachment (dreamquartz database embedded)

dreamquartz,

i think that Villeroy has in fact already given the solution to your issue.
it's often more difficult to decipher your posts than it is to answer them.
you also failed to tell us that your using hsqldb 2.2.4.

anyway if i assume that the question is:-
i have 2 tables.
"tPerson": "PersonID", "Surname", "GivenName".
"tStaff": many fields including "FKPersonID" foreign key references "tPerson"."PersonID".

when using a form to add / edit entries in the table "tStaff" i use a list box in order to select a person from the table "tPerson".
everything works OK but because the field "FKPersonID" in the table "tStaff" is an integer i am unable to sort by "tPerson"."Surname".
any suggestions?

Solution:
copy the code below & paste it here >>> 'Queries>Create Query in SQL View'.
DO NOT select 'Run SQL command directly'.
save & use as the data source for your form.

Code: Select all

select S.* from "tStaff" S order by (select "Surname" from "tPerson" where "PersonID" = S."FKPersonID")

if i have misunderstood then i apologise.

[dreamquartz]

Your database is not readable. It is an embedded HSQL2 database with passwod protection. If you set the hsql2 driver in the global office options, you can not use embedded HSQLDB anymore. I'm fine with HSQL2 but then there should be no protected user account.

So,
Created an embedded db, and saved it that way as well.
Yes I am using external 2.3.4.

Never had that one.
What is the cause, and how to prevent it?

Dream

[dreamquartz]

Edit: 18-Jan-17 02:06 have added attachment (dreamquartz database embedded)

dreamquartz,

i think that Villeroy has in fact already given the solution to your issue.
it's often more difficult to decipher your posts than it is to answer them.
you also failed to tell us that your using hsqldb 2.2.4.

anyway if i assume that the question is:-
i have 2 tables.
"tPerson": "PersonID", "Surname", "GivenName".
"tStaff": many fields including "FKPersonID" foreign key references "tPerson"."PersonID".

when using a form to add / edit entries in the table "tStaff" i use a list box in order to select a person from the table "tPerson".
everything works OK but because the field "FKPersonID" in the table "tStaff" is an integer i am unable to sort by "tPerson"."Surname".
any suggestions?

Solution:
copy the code below & paste it here >>> 'Queries>Create Query in SQL View'.
DO NOT select 'Run SQL command directly'.
save & use as the data source for your form.

Code: Select all

select S.* from "tStaff" S order by (select "Surname" from "tPerson" where "PersonID" = S."FKPersonID")

if i have misunderstood then i apologise.

Sorry, but no that does not work.

I went for the principle approach as well, but everything changed to a list principle.
This meant that that was recorded, was listed, but could not add/update Staff.

I just have a list of people, and need to make a selection, to elevate a specific selected person to a Staff status. It will give that person a new unique identifier, called StaffNumber.
Under StaffNumber and FKStaffNumber there are different items saved.
In a simple form/sub_form, it is very easy to show all in a "Table Control", but Staff is literally just a re-presentation of the FKPersonID, stored in tStaff.

Dream

[Arineckaig]

dreamquartz:

I just have a list of people, and need to make a selection, to elevate a specific selected person to a Staff status. It will give that person a new unique identifier, called StaffNumber. Under StaffNumber and FKStaffNumber there are different items saved.

This would now appear to be a OneToOne relationship where each of the records in the 'tStaff' table references a specific record in the more numerous 'tPerson' table. It could well be incompatible with the original aims, as the only way to

create a fill out form and a reference form, but would like to keep it in one place, and use one form.

and to

make the information from the source table tPerson available to tStaff for that purpose?

would be a form sourced from an Outer Join query: somewhat cumbersome as the display would have to include as many records as are in the 'tPerson' table but with only a few fields filled from the 'tStaff' table. The form/sub-form route could well be preferable where, depending on its design, the appearance of the document could well match that of a single form.

[dreamquartz]

Hi All,

Thanks for all the comments and time you spent helping to sort this one out for me.

What I found was that if the source of the form fStaff is set to and SQL statement

Code: Select all

SELECT "tPerson".*, "tStaff".* FROM "tStaff", "tPerson" WHERE "tStaff"."FKPersonID" = "tPerson"."PersonID"

all of the different records can be sorted, but also entered and manipulated via the 'table control' and all the Boolean tickboxes (see form !fStaf19-01-2017@12.14PM).

Dream

[Arineckaig]

What I found was that if the source of the form fStaff is set to and SQL statement

Code: Select all

SELECT "tPerson".*, "tStaff".* FROM "tStaff", "tPerson" WHERE "tStaff"."FKPersonID" = "tPerson"."PersonID"

all of the different records can be sorted, but also entered and manipulated via the 'table control' and all the Boolean tickboxes (see form !fStaf19-01-2017@12.14PM)

A neat approach, but you may find potential for error if ever changing an existing, as opposed to adding a new, entry in the "Staff" column. Some care is required to reflect the elementary constraints imposed by the Base GUI to ensure referential integrity between tables.