Sorting multiple fields as on in report

[PizzaBoy]

Hello Group,

I have a report based on a table in which each record contains a unique ID
number and fields for up to three owners in each record. I would like to
have a report which lists all three owners as if each was an individual was
a unique record instead of three per record. I then want to sort the report
based on last name. Currently if I sort the report I can only sort by one of
the three name fields. Any help is greatly appreciated. Thank you.

 

[Steve Schapel]

PizzaBoy,

Before considering the specific answer to your question... Is the
design of the database set in concrete, or is it possible that it can be
changed, so you have a spearate owners table, related one-to-many with
the existing table. This is probably how it should have been
constructed inthe first place, to comply with database design principles.

 

[PizzaBoy]

The database is not set in stone. Simply put the clearest resolutions is to
correct the database design and not work around it in the report? I will
probably adapt the design. Is my question a possibility to do in a report
without code?

Thanks.

 

[Van T. Dinh]

Yes.

 

[John Vinson]

Hello Group,

I have a report based on a table in which each record contains a unique ID
number and fields for up to three owners in each record. I would like to
have a report which lists all three owners as if each was an individual was
a unique record instead of three per record. I then want to sort the report
based on last name. Currently if I sort the report I can only sort by one of
the three name fields. Any help is greatly appreciated. Thank you.

A UNION query can do this - and can serve as the basis of a way to
normalize your data as Steve suggests. You give up the ability to edit
the data with this, but you can include your table three times in a
UNION query in the SQL window.

Create a new query; go to the SQL window and edit it to something like

SELECT UniqueID, Owner1 AS Owner, <other fields>
FROM yourtable
WHERE Owner1 IS NOT NULL
UNION
SELECT UniqueID, Owner2, <same fields>
FROM yourtable
WHERE Owner2 IS NOT NULL
UNION
SELECT UniqueID, Owner3, <same fields>
FROM yourtable
WHERE Owner3 IS NOT NULL
ORDER BY (2);

to select multiple copies of the records, ordered by the second field
([Owner] that is).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps