Sort order in a form help

  • Thread starter Thread starter grog
  • Start date Start date
G

grog

Hi All,

Thanks in advance. I have a clients table and a building tables joined
by a one to many relationship. I have 6 records in the Clients table
and 2 records in the buildings table - both associated with the same
Client

I wsh to create a form to display the 6 records of the Clients table
(and Buildings records in a subform) and specify the sort field.

BUT 1. If I base my form on the Clients table, I do not see how to set
the sort order.
2. If I first create a query and base the form on this query, I can set
the sort field in the QBE grid. The problem is that the query does not
return the number of records that I expect. If I use a regular join in
the query, there are only two records in the dynaset. (Where Buildings
and Clients exist and have the same B ID) If I make a left join then I
get too many records - 7(including one for each builidng).

Can anyone help?

thanks

Clive Grog
 
Clive, I'm not clear about your logic here.

One Client has many Buildings.
You want unique clients, sorted by a field in the Buildings table.
How do you determine *which* of the many records in the Buildings table for
a client to grab and apply this sorting to?

The solution might involve a subquery in the ORDER BY clause.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Back
Top