Grouping multiple rows

AccidentalFate

Registered User.
Local time
Yesterday, 19:44
Joined
Apr 28, 2008
Messages
42
What i am trying to do is group the raceways and have one row for each raceway... do this by grouping the packages and seperating them by ", "

is this possible in a query or do i have to use a module?


group.jpg


so i want it to look like this

Raceway Package
1EECBX01 1APC-01, 1BMC-01, 1CCC-01, 1CCC-02, 1CCC-03, 1CCE-01, 1CCE-02, 1COA-01, 1WSC-01, CSTG-03
 
the second one looks like what i need... but its a little complex for me to edit myself... would you be able to help me out?
 
Actually I would lean towards the first solution, simply because then you have something that can be called from anywhere. If you code it up in a report and later need it elsewhere, you have to code it all again. Here's a sample I did for somebody hopefully tweaked for you:

Code:
Public Function Concat(Raceway As String) As String
  Dim rs As DAO.Recordset
  Dim strSQL As String
  Concat = ""
  strSQL = "SELECT Package from [Raceway Package2] WHERE Raceway = '" & Raceway  & "';"

  Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  Do While Not rs.EOF
    Concat = Concat & rs!Package & ";"
    rs.MoveNext
  Loop
  Concat = Left(Concat, Len(Concat) - 1)
  Set rs = Nothing
End Function
 
hmm... i don understand any of that lol... i made a mod and inserted the data... no error but i couldn't see wat it did anywhere... where should i look?
 
Try a query:

SELECT Raceway, Concat(Raceway) AS PackageList
FROM TableWithJustRaceways

I'm assuming you have a table of the raceways. If you still have trouble and can post a sample db, I can make it work.
 
qryPaul, so I'll be remembered forever. :p

Seriously, name it anything you want. It doesn't matter to the function.
 
15mb won't fit here (though you can see if compacting it before zipping it gets it down far enough). How about exporting the main table and the raceways table if there is one to a new db, and posting that?
 
Unless I'm missing something, that table ("Raceway package2") is not in this db, and the table that looks similar only has 1 instance of each raceway in it, not multiples as in your example. Which table are we looking at, and what exactly do you want from it?
 
Gotcha. By the way, one thing I already had to fix is that the module name can't be the same as the function name. In your db, rename the module to something like basConcat or modConcat or whatever fits your naming convention.
 
i remember i deleted it to make it fit...

to get that query i...

1. ran the "raceway circuit split" module
2. made a query "raceway package" with this sql
Code:
SELECT Packages.ID, Packages.Raceway, Packages.Circuit, Circuit.Package
FROM Packages INNER JOIN Circuit ON Packages.Circuit = Circuit.Circuit;
3. made "raceway package2" with this sql
Code:
SELECT [Raceway Package].Raceway, [Raceway Package].Package
FROM [Raceway Package]
GROUP BY [Raceway Package].Raceway, [Raceway Package].Package;
 
i renamed the module Concat so you knew which one was the one you made... its somethin different in mine...
 
wow... i'm special... lol... i must have done something wrong... i redid everything and it worked...

thanks so much
 
Excellent, glad to hear it.
 
there is one thing... when there isn't a circuit in the raceway it comes up with an error... is there a way to keep this from happening? like when blank return blank or something?
 

Users who are viewing this thread

Back
Top Bottom