SQL Solution Needed - Use Subquery to move groups of rows to columns

[HouBMan]

Below are 2 tables/queries: one is the source the other is the desired
result. I scoured the newsgroups and my access books - seems like
queries with subqueries and/or a possible a crosstab query on is in
order. I'm stuck on how to get started though.

Any help would be great.

SOURCE TABLE OR QUERY
ToolID CompanyName TicketDate DeliveryTicket RecievingTicket
1 Company 1 3/2/2009 No Yes
1 Company 2 3/3/2009 Yes No
1 Company 2 7/21/2009 No Yes
1 Company 2 7/28/2009 Yes No
1 Company 2 8/4/2009 No Yes
1 Company 3 9/1/2009 Yes No
2 Company 1 3/2/2009 No Yes
2 Company 2 3/3/2009 Yes No
2 Company 2 6/4/2009 No Yes
2 Company 3 6/9/2009 Yes No
2 Company 3 6/23/2009 No Yes
2 Company 4 6/25/2009 Yes No
2 Company 4 8/26/2009 No Yes
2 Company 5 9/15/2009 Yes No


DESIRED QUERY RESULTS
ToolID CompanyName Delivery Date Rec'd Date
1 Company 2 3/3/2009 7/21/2009
1 Company 2 7/28/2009 8/4/2009
2 Company 2 3/3/2009 6/4/2009
2 Company 3 6/9/2009 6/23/2009
2 Company 4 6/25/2009 8/26/2009

By the way, I didn't want to put too much info out there yet so if my
question needs clarifying please let me know.

 

[Clifford Bass via AccessMonster.com]

Hi,

The this which does a self join on the ToolID and the CompanyName
(CompanyID would be better) and uses a subquery to get the next Ticket date
for that ToolID and CompanyName. (Note spelling correction of
ReceivingTicket.):

SELECT A.ToolID, A.CompanyName, A.TicketDate, B.TicketDate
FROM tblToolTickets AS A INNER JOIN tblToolTickets AS B ON (A.CompanyName = B.
CompanyName) AND (A.ToolID = B.ToolID)
WHERE (((B.TicketDate)=(select Min(TicketDate) from tblToolTickets as C where
C.ToolID = A.ToolID and C.CompanyName = A.CompanyName and C.TicketDate > A.
TicketDate)) AND ((A.DeliveryTicket)=Yes) AND ((A.ReceivingTicket)=No) AND (
(B.DeliveryTicket)=No) AND ((B.ReceivingTicket)=Yes))
ORDER BY A.ToolID, A.CompanyName, A.TicketDate;

Clifford Bass

Clifford Bass

 

[HouBMan]

Cliff,
Thanks so much - I'm gonna look at this piece by piece and get back to
you with any questions - thanks!

 

[HouBMan]

Cliff,

Thanks again

I spent quite a bit of time disecting your answer but I got it! I
don't think I had any idea how powerful sub-queries could be. Now I
have a bit more of the idea.

Brett

 

[Clifford Bass via AccessMonster.com]

Hi Brett,

Glad I was able to help you and increase your knowledge. You are
welcome!

Clifford Bass