Can Parent/child be made flat?

[CatGut]

I have been using Access for several years; now I need to try something a little more complex...

I have been given a database that is a ticket-assignment system in which a single ticket (a record on the main database) can be assigned to multiple personnel - achieved by creating multiple records on the second database. They are joined by ticket number.
Data looks like this:
ticketnumber assignee
1 personX
2 personX
2 personY << ticket 2 repeated for second assignee
3 personY

My new task is to write reports. I am not allowed to change the structure of the data.
I am trying to write a query like this:
show me all the tickets that are NOT assigned to personX.

Now, I can do that if personX is the only record on the second database.
BUT if a single ticket is assigned to both personX and personY like ticket 2 above, then my query only eliminates the row containing personX and it leaves the row containing personY - but I don't want to see either row for ticket 2.

I was hoping there was a way to "flatten" the database so that the query would generate a single line for each ticket, and the multiple assignees would show up as columns side-by-side like this:
ticketnumber assignee1 assignee2 assignee3
1 personX
2 personX personY
3 personY
Then I could put a not-equals selection in each column and eliminate the tickets

Can this be done? Other ways I should approach this?

 

[bat17]

One way that I think would work would be to:-
1) Create a query to filter just the records for “x”
2) Create a GroupBy query to show just the tickets so that you only get one instance of each ticket
3) Create a query based on 1 and 2, using a Left join to show all records from 2 and only matching from 1, add the Ticket number field from 2 and the name field from 1.
Filter for IS Null on the name field.

This should just give you the tickets with out X

HTH

Peter

 

[CatGut]

Thank you thank you thank you!!! It took me 2 hours to figure out how to accomplish the 3 tasks that you gave me, but
1. it works perfectly and
2. I know a whole lot more about how to deal with complex databases.

Now I am going to try to solve another problem on my own. Hopefully you won't hear from me again on this list.

Although I will continue to monitor it and maybe I can help someone else.

Thanks!!!!!