If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
group/sort on 2 fields w/ same value
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? |
#2
|
|||
|
|||
group/sort on 2 fields w/ same value
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. "Paul" wrote: 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? |
#3
|
|||
|
|||
group/sort on 2 fields w/ same value
Paul wrote:
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 -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|