Printing Records in Report by Officer Rank 2

[pappion]

I have 13 committee member records which I need to print in a 2 column report as follows:

Name
Title (President/Vice President/Treasurer/Secretary)
Address
City, State Zip,etc.

First 4 records in report should be the President, Vice President, Treasurer, Secretary. How do I get these two print first (in rank order) followed by the other members of the committee - in alpha order and without a title field displayed for the remaining members? Caution - I am a newbie.

 

[Remou]

It may be possible to do this with a union query. Something like:

[tt]SELECT 1 As SortOrd, SName & (Chr(13) + Chr(10)) & Title, ..., Zip FROM tblTable WHERE Title="President"
UNION ALL
SELECT 2 As SortOrd, SName & (Chr(13) + Chr(10)) & Title, ..., Zip FROM tblTable WHERE Title="Vice President"
UNION ALL
SELECT 3 As SortOrd, SName & (Chr(13) + Chr(10)) & Title, ..., Zip FROM tblTable WHERE Title="Treasurer"
UNION ALL
SELECT 4 As SortOrd, SName & (Chr(13) + Chr(10)) & Title, ..., Zip FROM tblTable WHERE Title="Secretary"
UNION ALL
SELECT 5 As SortOrd, SName, ..., Zip FROM tblTable WHERE Title Not In "President, Vice President, Treasurer, Secretary"[/tt]

Or there abouts.

 

[dhookom]

I would create a table of Titles with a sort order field. You can then join this table into your reports record source with a join that includes all records from your un-named table. Sort on an expression like:
=Nz([SortValue],9)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pappion]

Thanks for the responses - I entered the following whicn sorted properly.

The "Board Members" do not appear in alpha - order (how can I do this is it even possible?)

IIf([CommitteePosition] = "President, ", 1, IIf([CommitteePosition] = "Vice President", 2, IIf([CommitteePosition] = "Treasurer", 3, IIf([CommitteePosition] = "Board Member", 4, 5))))

 

[dhookom]

I realize alpha order would not work so you would need to have a table that stores the position and a value 1-5.

You could try an expression in the sorting and grouping like:
=Instr("PresidentVice PresidentTreasurerBoard Member",[CommitteePosition])



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]