Sort main table by fields in linked table

[Peter]

I can't believe I'm the only person with this problem, but the
archives don't seem to have much devoted to the subject. Maybe I'm
just doing a bad job of searching.

I have a main table of specimens with many linked tables of such things
as genus, species, locality, country, discoverer and so on. They are
joined in 1-M relationships, and the auxiliary tables serve largely to
populate comboboxes on the main data entry from. I need to create
pre-defined sorts and filters for various uses. The filters were fairly
simple, even when filtering by criteria not in the main table, by using
clauses like

"IDCislo IN (SELECT Matrix.IDCislo FROM Matrix)"

or

"IDCislo IN (SELECT IDCislo FROM FilterLokalita)"

to select only those records in the main form whose corresponding
record in a linked table has some specific value or property. However,
I can't seem to do anything comparable for sorting. That is, I want
to sort the records in the main from by a field OTHER THAN the primary
key of a linked table, which I don't have directly available, the
same way I filter by values that I don't have directly available.

I found one post from Allen Browne, back in 2004, that advised someone
to set up a query that includes those fields, with certain conditions
applied (no default values). I tried that, but the linked tables have
required fields, which causes addition of records to bomb. When I make
the fields not required, adding a main record also adds a record with
empty field to the linked table.

Is this a lost cause or am I just being stupid (again)?

Pete

 

[Douglas J. Steele]

Join the two tables in a query, and you'll have the fields from both tables
available to you:

SELECT Table1.IDCislo, Table1.Field2, Matrix.Field2, Matrix.Field3
FROM Table1 INNER JOIN Matrix
WHERE Table1.IDCislo = Matrix.IDCislo

If you want all records from Table1, regardless of whether or not they exist
in the second table, use a LEFT JOIN.

 

[Peter]

Thank you, Doug, I did that, but if you read further, I had other
problems with that approach. I did use the LEFT JOIN, since not all
main records have corresponding linked records, but the problem with
adds or empty records was there anyway.

Pete



Douglas J. Steele napsal:

 

[Douglas J. Steele]

You'll need to provide more details about the design of the tables in
question, then.

Not to quibble, but your original post didn't indicate that you'd tried a
LEFT JOIN.

 

[Peter]

Hi Doug.

Thank you for your advice and of course you're right, I didn't specify
that. It's sometimes tough to remember what all details I need to
include in a post.

Anyway, let's see if I can make it all clearer.

There is one main table, describing lichen specimens. It has a bunch of
auxilliary tables attached, all in one to many relationships, linked by
autonumber fields in the aux tables.

The aux tables are generally just the autonumber key field and an
additional text field, by which I would like to sort the main table.
Referential integrity between main and aux tables is enforced, but not
cascaded deletes. Both fields in the aux tables are required, the key
(obviously) and the text as well, since there is no point in having a
record in those tables if there isn't a value in it.

The aux tables are used to fill comboboxes, where the user can select
taxonomic specification, country of origin, name of discoverer and many
other details. The aux tables have simple forms which allow editing
their contents as well, but most of the action takes place on one form,
which is where I want to present the main records in various orders.

When I use JUST the main table (ZapisNalezu) for the form's
recordsource, everything on the form works, but I have no way of
sorting the main records by the contents of the aux tables' non-key
fields. When I use a query like the one below, I can sort on the fields
just fine, but I can't then add new records to the main table, since
the query of the combined tables sees a required field left blank. When
I make the text field in the aux table not required, adding a new main
record also adds a new record to the aux table, with blank in the text
field. Clearly, neither of these is acceptable.

SELECT ZapisNalezu.*, Akcesity.Akcesit, Druhy.Druh, Lokalita.Lokalita,
PrirodniCelky.PrirodniCelekCesky
FROM PrirodniCelky RIGHT JOIN (Lokalita RIGHT JOIN (Druhy RIGHT JOIN
(Akcesity RIGHT JOIN ZapisNalezu ON Akcesity.KodAkcesitu =
ZapisNalezu.KodAkcesitu) ON Druhy.KodDruhu = ZapisNalezu.KodDruhu) ON
Lokalita.KodLokality = ZapisNalezu.KodLokality) ON
PrirodniCelky.KodPrirCelku = ZapisNalezu.KodPrirCelku;

Looking at the query now, though, I see that it uses a RIGHT JOIN
rather than LEFT JOIN as you specified. This is from the query builder,
though, and if I turn the arrows around, the query doesn't work at
all. Is there a difference between TableA LEFT JOIN TableB and TableB
RIGHT JOIN TableA?

Is this enough to detail to let you know what I'm doing? If not, what
more do you need? I'll be happy to provide whatever you need to know,
but I don't want to bombard you with pointless trivia.

I'll be gone over the weekend, but Monday I will check back again.

Thank you for your time,

Pete




Douglas J. Steele napsal:

 

[Peter]

Hello again, Doug.

I've solved my problem, and as usual, in such a simple way that I
feel like a dunce for not trying it sooner. I simply selected one of
the comboboxes on my form, then clicked the sort icon on the toolbar,
switched to design view and looked in the "OrderBy" field of the
form's properties. There I found "Lookup_cboAkcesit.Akcesit",
which does exactly what I want: gives me access to a foreign field by
which to sort. With this info, I have been able to construct all the
special multiple-field sorts with foreign fields that my application
needs. I have read bad things about lookup field speeds and have had
some such negative experiences myself during various experiments, but
this works great. It's not a large database; just over six thousand
records, but even on multiple foreign fields, results of a sort are
instantaneous.

So I'm in business with my application, but still curious why the
query approach you suggested didn't work, since I may need to use
such a device in the future. If you are willing, I would still like to
track down the problem there.

Pete


Douglas J. Steele napsal: