Link to home
Start Free TrialLog in
Avatar of Cory Vandenberg
Cory VandenbergFlag for United States of America

asked on

Aggregating multiple rows and concatenating multiple entries for one variable

Hello,

I have a query of the following format:
ProjMgr     ProgMgr     Coach     ProjAdm                    CompanyID               WSDate
                DRGroover                                                156987                     5/1/2006
                                 BFaust                                     156987                     5/1/2006
                                 SLuttrull                                   156987                     5/1/2006
                                               AJDockstad      156987                                     5/1/2006

-------
There are many companies and some have multiple workshop dates (WSDate).  I would like to aggregate the data by CompanyID and WSDate so that each company has one line for each workshop date.  There may be multiple Coaches or ProjAdm for one company/workshop date, so in that case I need to put all of them in the same entry, delimited with commas for instance.

Is there a way to do this in Access using the design view or SQL?  Thanks for any help in advance.

WC
Avatar of Raynard7
Raynard7

You can do this; it depends on your table structure;

But basically start with the WS date table on the left - in design view - then add the links for each of the projadm, coach, and manager and you will get one row for each ws date and the rest will be populated with respect to that.
Avatar of Cory Vandenberg

ASKER

The database is set up in a way where we have a variable: chUserId, which stores an integer value designating if the user is a ProjMan, ProgMan, Coach, or ProjAdm.

Here is the code:  There are some other variables, not included in my set up of the problem.
-----------
SELECT IIf(dbo_vContactInternal.iContactTypeId=12,dbo_vContactInternal.chUserId,) AS ProjMgr, IIf(dbo_vContactInternal.iContactTypeId=63,dbo_vContactInternal.chUserId,) AS ProgMgr, IIf(dbo_vContactInternal.iContactTypeId=53,dbo_vContactInternal.chUserId,) AS Coach, IIf(dbo_vContactInternal.iContactTypeId=35,dbo_vContactInternal.chUserId,) AS ProjAdm, [T3: List of Locations with LDI Leaders].OnyxCompanyID, [T3: List of Locations with LDI Leaders].vchCompanyName, [T3: List of Locations with LDI Leaders].vchAddress1, [T3: List of Locations with LDI Leaders].vchCity, [T3: List of Locations with LDI Leaders].chRegionCode, [T3: List of Locations with LDI Leaders].chCountryCode, Min(dbo_Hours_Recordables.FoM) AS MinOfFoM, Max(dbo_Hours_Recordables.FoM) AS MaxOfFoM, [T3: List of Locations with LDI Leaders].WSDate
FROM (([T3: List of Locations with LDI Leaders] LEFT JOIN dbo_vContactInternal ON [T3: List of Locations with LDI Leaders].OnyxCompanyID = dbo_vContactInternal.iOwnerId) LEFT JOIN dbo_Projects ON [T3: List of Locations with LDI Leaders].OnyxCompanyID = dbo_Projects.iCompanyID) LEFT JOIN dbo_Hours_Recordables ON dbo_Projects.ProjectNo = dbo_Hours_Recordables.ProjectNo
WHERE (((dbo_vContactInternal.iContactTypeId) In (12,35,53,63)))
GROUP BY [T3: List of Locations with LDI Leaders].OnyxCompanyID, [T3: List of Locations with LDI Leaders].vchCompanyName, [T3: List of Locations with LDI Leaders].vchAddress1, [T3: List of Locations with LDI Leaders].vchCity, [T3: List of Locations with LDI Leaders].chRegionCode, [T3: List of Locations with LDI Leaders].chCountryCode, [T3: List of Locations with LDI Leaders].WSDate, dbo_vContactInternal.chUserId, dbo_vContactInternal.iContactTypeId
Ya, sorry, but iContactTypeId stores the integer that designates the role of the user, but you could probably tell that from the Iif statements.
So what I'm looking for is for the data in the setup to come out looking like:

ProjMgr     ProgMgr     Coach                ProjAdm                    CompanyID               WSDate
                DRGroover  BFaust, SLuttrull  AJDockstad               156987                     5/1/2006
So there may be only one ProjMgr, always only one progMgr, never more than two Coches, and always only one ProjAdm?
Before I offer a solution, does the result need to be a query, a table, a form, or a report?
you will need to create a temporary table same structure as your original table then transfer your data, base on CompanyID and WSDate, and other fields are comma separated, then you link this temporary table  for report or query as you wish
There is only one ProjMgr and ProgMgr, but there may be multiple Coaches and/or ProjAdm's.  The result should be in a query.
Assuming your query above is saved as myQry, try this.  This will give you the Company ID, the ProjMgr, the ProgMgr and up to three of each of Coaches and ProjAdms.  Any more, and the qeury gets too complicated.  Unfortunately there may be a trailing ', ' when you have less that three Coaches or ProjAdms.

Select a.CompanyID, a.ProjMgr, a.ProgMgr,
a.Coach & ", " & (Select First(b.Coach) & ", " & (Select Last (c.Coach FROM myQry AS c WHERE c.Coach <> b.Coach, AND c.Coach <> a.Coach AND c.CompanyID=a.CompanyID)  FROM myQry AS b Where b.Coach <> a.Coach and b.ComanyID=a.ComanyID) AS Coaches,

a.ProjAdm & ", " & (Select First(b.ProjAdm) & ", " & (Select Last (c.ProjAdm FROM myQry AS c WHERE c.ProjAdm <> b.ProjAdm, AND c.ProjAdm <> a.ProjAdm AND c.CompanyID=a.CompanyID)  FROM myQry AS b Where b.ProjAdm <> a.ProjAdm and b.ComanyID=a.ComanyID) AS ProjAdms

FROM myQry AS a ORDER BY a.CompanyID;
I'm getting missing ) error messages.  I see there is 1 missing ) in each statement, just can't figure out where it belongs.

Also, is there a more dynamic solution to this, as I have instances of up to 6 Coaches.
Can I suggest
1 A query by CompanyID that includes only the Coaches (since this is where you may have duplicates)
   Maybe: SELECT Staff.Company, Staff.Coach FROM Staff WHERE (((Staff.Coach) Is Not Null));

2 A function that strings the coaches when provides a company ID

3 A new string expression in the query with the remaining fields that uses the new function for the coaches.  
   This third query could be an SQL string like this:
SELECT First(Staff.ProjMgr) AS ProjMgr, First(Staff.ProgMgr) AS ProgMgr, GetCoaches([Company]) AS Coaches, First(Staff.ProjAdm) AS ProjAdm, Staff.Company, Staff.IDWSDate
FROM Staff
GROUP BY GetCoaches([Company]), Staff.Company, Staff.IDWSDate;

Anyone up to the Function challenge?  I can attempt one after lunch.
BTW, 1 and 2 above would also be needed for ProjAdm.
  The query: SELECT Staff.Company, Staff.ProjAdm FROM Staff WHERE (((Staff.ProjAdm) Is Not Null));

The function call for GetProjAdms([Company]) to look at a different field.
With up to six coaches you can only do this realistically from within VB. ie.  You need to create a function that concatinates your Coaches and ProjAdms:

Public Function Concat(CoyID as Integer, fld as String) as String

Dim mydb as DAO.database, myrs as DAO.recordset, strSQL as String
strSQL="SELECT * FROM myQry WHERE " & fld & " = " & CoyID & ";"

set mydb=currentdb
set myrs=mydb.openrecordset(strSQL)
myrs.movelast
myrs.movefirst
do while not myrs.eof
  Concat = Concat & iif (fld="Coach",myrs!Coach,myrs!ProjAdm) & ", "
  myrs.movenext
loop
set myrs = nothing
set mydb = nothing
concat = left(cocat, len(concat)-2)

End Function

Call the function with:

Select CompanyID,ProjMgr, ProgMgr, Concat(CompanyID, "Coach"), Concat(CompanyID,"ProjAdm") FROM myQry GROUP BY CompanyID ORDER BY CompanyID;
I should have mentioned, this assumes your query above is named myQuery, and all the fields as mentioned in your quesition exist as such in the query.
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
GR,

I've worked with ASP driven webpages and VBA before so this is a much more comortable approach for me.  I can follow your code clearly.  I was a little confused before w/ the comparison of an int and str value.  I see now what you were trying to do.   I am still having problems, and for some reason, the VBA Editor is not stopping on my breakpoints.  Yesterday it did the first few times, but now I can't debug the code.  Any ideas on that?
I see a spelling mistake:

concat = left(cocat, len(concat)-2)

should be

concat = left(concat, len(concat)-2)

In VB Editor, click Options, References and make sure none are marked as missing.  Then try a compact and repair.  That failing, open a new mdb, and import all the objects from this mdb into the new one.  Are you sure you do not have the code in two places, maybe in two different modules?  
Ya, I caught the typo.  compact and repair?  Is that the same as Detect and Repair?  Never had this problem before.  This is the ONLY module in the whole database.  No one else here uses VBA.
Apparently it doesn't like the first parameter I'm passing into the function.  I put an integer in instead of the field name and the debugger works.
The integers I'm using are over 50000.  Anytime I type an integer this size in instead of the field name I get a pop up error message:  Overflow.   I'm guessing that is why the debugger isn't working when the field name is in there.  Any ideas besides upsizing to SQL server which is not an option.
Nevermind, it was the Integer definition.  I switched it to a Double.

I'm getting an error on the line
Set myrs = mydb.OpenRecordset(strSQL)

It's saying, too few parameters. Expected 1.

this is the value of strSQL = "SELECT ProjAdm FROM myQry WHERE CompanyID = 52364;"
which is an entry in the myQry.

Is it because the field is empty?  Should I do a check for that somewhere?
I've tried this, not working though.

strSQL = "SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & " AND " & fld & "<> '';"
Those are single quotes representing an empty string, also used NULL there before the ;
No, you should change it to Long.
Given CompanyID is a long integer, you should be using this construct.  If CompanyID were a string you would need the single quotes but that is not the case.  The fields Coach and  ProjAdm are passsed by the variable fld, and as CompanyID is never null:

strSQL="SELECT " & fld & " FROM myQry WHERE CompanyID " = " & CoyID & ";"
A little confusion there, sorry.  I'm comparing fld to null, not CompanyID

strSQL="SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & " AND " & fld & "<>NULL;"
It should not matter that either field is null.  The ampersand takes care of those cases

? "War" & null & "Crimes"
WarCrimes
The line
myrs = mydb.OpenRecordset(strSQL)

That's looking for a recordset from the database, where the recordset is defined by the SQL statement.  It gives me an error,"Too few parameters. Expected 1", which leads me to believe the Recordset is not being constructed properly.  Any ideas why?  My thoughts were that null values of Coach or ProjAdm may have been causing problems, but maybe it is something else.

As for the ampersand, I know those are just concatenation symbols, they have nothing to do with a comparison to null for one of the fields.  I'm not checking that fld is Null, I'm checking the value stored in the parameter passed from the recordset is null.  Whether that is necessary or not, I'm not sure.
Using the Debugger, insert a breakpoint after creating the string, run the code, and examine the string.  Alternatively, insert the line:  debug.print after generating the string.  This will print out in the Immediate pane from which you can copy and paste into a post in this thread.
GRayL,

Ya so in an attempt to keep confidentiality with our companies database I was changing names and it was causing problems in the function.  I worked out the variable names issue, and also put in conditionals and trim function to tidy up the data.  The code does what I want it to now, but I must say it is EXTREMELY slow.  Any ideas on how to make the code more efficient, or in other words less processor intensive?  If you try to scroll the query you have to wait for it to run all over again.  The only solution is to let it run, copy/paste into excel and work with it there, which is doable.

In any case.  Thanks for your help, and if you have any further suggestions, please pass them on.  I am giving you the points for your solution, which I have tweaked and is posted below.
Public Function Concat(CoyID As Double, fld As String) As String

Dim mydb As DAO.Database, myrs As DAO.Recordset, strSQL As String
strSQL = "SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & ";"
Set mydb = CurrentDb
Set myrs = mydb.OpenRecordset(strSQL)
myrs.MoveLast
myrs.MoveFirst
Do While Not myrs.EOF
  If myrs.Fields(0).Value <> "" Then
    Concat = Concat & Trim(myrs.Fields(0).Value) & ", "
  End If
  myrs.MoveNext
Loop
Set myrs = Nothing
Set mydb = Nothing
If Len(Concat) <> 0 Then
  Concat = Left(Concat, Len(Concat) - 2)
End If

End Function
Thanks, glad I could help.  As to speed, try creating indexes on CompanyID, Coach and ProjAdm.  Got to run - sailboat racing in an hour.