One Table, Multiple IDs, Multiple Units 2

[mpm32]

I think I have read and tried every post and FAQ on this subject. Nothing worked for me yet it seems so simple.

I have one table - APUnits

This table has three fields.

ID Unit Amount

I want to write a query that takes this data

ID Unit Amount
1 ABC $1500.00
1 DEF $1200.00
2 ABC $500.00
2 DEF $750.00
2 GHI $125.00

And output this for the Unit field to be used on a report;

ID Unit
1 ABC, DEF
2 ABC, DEF, GHI

I have tried Duane's Concatenate and some other functions and all I get with them is an "Undefined Function" error.

This seems so simple and I have done much more complex things but this has me stumped.

Thanks in advance,

Mark

 

[sfreeman]

I think you are focused in the wrong area. Instead of trying to make your query do the grouping, do it in your report.

A simple query such as:

SELECT
APUnits.ID
, APUnits.Unit

FROM
APUnits;

works OK. Use the report wizard and be sure to group by the ID field. Your units would normally appear in a column [not a row] below each ID.

ID Unit
1 ABC
DEF
2 GHI
JKL
MNO

Does that work for you?





Sam_F
"90% of the problem is asking the right question.

 

[lespaul]

Is the function declared as "Public" in the module?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[dhookom]

mpm32,
You didn't respond to my reply in the thread where you had asked this same question. What did you name your module containing the concatenate function?

You now have two threads going on the same question.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpm32]

Sam,

The idea you posted will now work for me because the field is just a small part of a larger report. Thanks though.

Leslie, the module is declared as public.

Duane, Sorry, I did not see your reply and someone mentioned posting issues in a new post.

Anyway, I copied everything from your function including the name. Still does not work.

Thanks for your help.

Mark

 

[dhookom]

mpm32,
What....did.....you.....name.....the.....standard.....module.....that contains......the......function?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpm32]

I named it basConcatenate, I also tried just Concatenate.

Still no luck.

 

[dhookom]

basConcatenate should work. Concatenate would not work and would cause the error you have seen.

If you are still having an issue getting Concatenate() to work,
-Press Ctrl+G to open the debug window
-enter the following

? Concatenate("SELECT Name FROM msysobjects WHERE Type=1")

You should see a comma delimited list of all tables in your mdb.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpm32]

Duane,

I got it to do something in my query but I get this now;

Runtime Error '3078'

The Microsoft Jet Database cannot find the input table 'PBCC'. Make sure it exists and is spelled correctly.

PBCC is one of the variables in the field that I want to concatenate.

Does/can your function work with just one table?

I have the code configured like this for DAO;

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
       If Not .EOF Then
          .MoveFirst
         Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

My query looks like this;

SELECT [AP Business Units].ID, Concatenate([Unit],",") AS Units
FROM [AP Business Units];

Any suggestions? I really appreciate your help.

Mark

 

[dhookom]

The concatenate function expects a sql statement as the first argument. I doubt "[Unit]" contains a sql statement.
Try:

SELECT ID, Concatenate("SELECT UNIT FROM APUNITS WHERE ID=" & [ID]) As Units
FROM APUNITS
GROUP BY ID, Concatenate("SELECT UNIT FROM APUNITS WHERE ID=" & [ID]);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpm32]

Duane,

Awesome. Works great now.

Thanks for helping me out. Now on to learn how you wrote the code and what it's actually doing for future reference.

Mark