Query

[Vinnie_AD]

I have data that looks like this…
Openings Qty Location 1
100 1 STAIR
101 1 EXTERIOR
102 1 CORRIDOR
103 1 STAIR
104 1 EXTERIOR
105 1 CORRIDOR
106 1 CORRIDOR
107 1 CORRIDOR
108 1 CORRIDOR
109 1 CORRIDOR
110 1 DINING


That I want to query like this…
2 STAIR 100. 103
2 EXTERIOR 101, 104
6 CORRIDOR 102, 105, 106, 107, 108, 109
1 DINING 110

Can you help?

 

[John Spencer]

You can use Duane Hookom's concatenate function to get the results you want

SELECT Location
, Sum(Qty) as CountThem
, Concatenate("SELECT Openings FROM YourTable WHERE Location = '" & location &
"'") as Openings
FROM YourTable
Group By Location
, Concatenate("SELECT Openings FROM YourTable WHERE Location = '" & location &
"'")


URL for sample of VBA function needed - (that should all be on one line)
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=194d6e26fad656da38382eba29349895


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County