group/sort on 2 fields w/ same value

[Paul]

I need to have my report group/sort multiple records based on two different
fields having the same value.

It's ticketing system and there is a Primary and Secondary individual
assigned to each ticket.

I'm trying to build a report that will group and list all records where an
individual is listed as EITHER the Primary OR Secondary. This will,
obviously, end up listing some ticket records twice and that is okay and what
I need (listed once under the Primary resource and again under the Secondary
resource).

Help?

 

[Golfinray]

Have a main form and 2 subforms. The primary and secondary would be subforms.
The subforms would go in the body of the report. Link master and child fields
to the main form (On the data tab in main form properties.) Set the header
property to force new page after or before section (depending on how you
format it) and it will print none. either, or both names on each page of the
report. You can then move the subforms around until you get the look you want.

 

[Marshall Barton]

I need to have my report group/sort multiple records based on two different
fields having the same value.

It's ticketing system and there is a Primary and Secondary individual
assigned to each ticket.

I'm trying to build a report that will group and list all records where an
individual is listed as EITHER the Primary OR Secondary. This will,
obviously, end up listing some ticket records twice and that is okay and what
I need (listed once under the Primary resource and again under the Secondary
resource).

Reports always start with the data. In this case you need a
union query to unravel the primary and secondary. I think
there is more to what you want than just this, but it should
get you started.

SELECT P.TicketNum, P.Primary, P.otherfield
FROM table As P
UNION
SELECT S.TicketNum, S.Secondary, S.otherfield
FROM table As S INNER JOIN table As X
ON S.Secondary = X.Primary
WHERE S.TicketNum Is Null