none
List question RRS feed

  • Question

  • I would like to take a list of items in a column in a separate and list them in a single fields.  Assuming that the list items are a child relationship. 

    text
    Bravo1
    Bravo2
    Bravo3

    so assuming that the above list and place all related fields into a single field like so (Bravo1, Bravo2, Bravo3).  Is this possible?

    Wednesday, September 16, 2015 12:48 PM

Answers

  • the DB guy, using your code provided to me I previous (SimpleCSV()), I inserted the following into a query, as such:

    
    SimpleCSV("SELECT [Organization]&':  '&[Number] FROM qryCrossReference WHERE [Case]=" & [Name])
    

    I receive an Runtime Error :  3464 Data type mismatch in criteria as a response.  Can you assist?

    Since DB Guy doesn't seem to be available, I will offer my assistance once more.

    Your problem is in two different places, the first is your concatenation of organization and number, your second is as the error message tells you, your criteria.  Again, it appears you are comparing a string in your WHERE clause, which means that you require quotations around the comparison value.  The following should hopefully solve your problem.

    Regards,

    SimpleCSV ("SELECT [Organization] & ':  ' & [Number] FROM qryCrossReference WHERE [Case]='" & [Name] & "'")

    Thursday, October 8, 2015 8:47 PM

All replies

  • I’m not sure, but based on your description it sounds to me like you want to design a Crosstab query where “text” is your Column Heading.

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, September 16, 2015 1:12 PM
  • No, that would not give you the "single field" the OP is asking for.

    This does, but the site is currently being repaired:
    http://access.mvps.org/access/modules/mdl0008.htm


    -Tom. Microsoft Access MVP


    Wednesday, September 16, 2015 1:48 PM
  • Hi. Pardon me... If you're trying to create a comma separated list from multiple records, then you might also take a look at this simple function: SimpleCSV()

    Hope that helps...

    Wednesday, September 16, 2015 2:59 PM
  • so assuming that the above list and place all related fields into a single field like so (Bravo1, Bravo2, Bravo3).  Is this possible?

    You need to post example of data with field name(s) and how you want the results - field names with data.

    Build a little, test a little


    Wednesday, September 16, 2015 4:36 PM
  • OK, I hopefully can clear this up:

    SUB Table                                     Projects Table
    ProjectID   Name                           ProjectID     Name
    2               Bravo1                         1                Project1
    2               Bravo2                         2                Project2
    1               Bravo3
    1               Bravo4
    1               Bravo5

    I want to combine the two in order to show the SUB Table as a list (concated together in a sense, separated by commas(maybe this is in a query)).  For example:

    query
    [Project Table].Name    [Sub Table].Name (in list)
    Project1                     Bravo3, Bravo4, Bravo5

    Hopefully this is clearer.


    Tuesday, September 22, 2015 12:41 PM
  • Did you try my solution

    http://access.mvps.org/access/modules/mdl0008.htm

    ?


    -Tom. Microsoft Access MVP

    Tuesday, September 22, 2015 2:16 PM
  • Good Morning Tom,

    Looks like the site is still down. Do you have the means to share your solution on OnDrive?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 22, 2015 3:42 PM
  • Good Morning Tom,

    Looks like the site is still down. Do you have the means to share your solution on OnDrive?



    Hi. I gave you an alternate link for the meantime. Did you try it?
    Tuesday, September 22, 2015 3:53 PM
  • Good Morning Tom,

    Looks like the site is still down. Do you have the means to share your solution on OnDrive?



    Hi. I gave you an alternate link for the meantime. Did you try it?

    Sorry DBguy,

    I thought you were talking to the OP.

    I was just letting Tom know the site is still down and didn't know your link was for the same solution as Tom's.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 22, 2015 4:49 PM
  • OK, I hopefully can clear this up:

    SUB Table                                     Projects Table
    ProjectID   Name                           ProjectID     Name
    2               Bravo1                         1                Project1
    2               Bravo2                         2                Project2
    1               Bravo3
    1               Bravo4
    1               Bravo5

    I want to combine the two in order to show the SUB Table as a list (concated together in a sense, separated by commas(maybe this is in a query)).  For example:

    query
    [Project Table].Name    [Sub Table].Name (in list)
    Project1                     Bravo3, Bravo4, Bravo5

    Hopefully this is clearer.


    Insert the following procedures into a standard module:

    Function GetProjectNames(ProjectID As Long) As Variant
        
        Dim varReturn As Variant
        Dim strResult As String
        Dim strSQL As String
        
        varReturn = Null
        
        strSQL = "SELECT SubProjName FROM SubProjects WHERE ProjectID=" & ProjectID
        strSQL = strSQL & vbCrLf & "ORDER BY SubProjectName"
        
        strResult = RecordsToCSV(strSQL, ", ")
        
        If (strResult <> "") Then
            varReturn = strResult
        End If
        
        GetProjectNames = varReturn
        
    End Function
    
    Function RecordsToCSV(SQLStatement As String, Optional Delimiter As String = ",", Optional FieldName As String) As String
            
        'Purpose: Reads a recordset and creates a csv string for the first or given field name
        
        On Error GoTo Err_Process
        
        Dim dbs1 As DAO.Database
        Dim rst1 As DAO.Recordset
        Dim strReturn As String
        
        strReturn = ""
        
        Set dbs1 = CurrentDb()
        Set rst1 = dbs1.OpenRecordset(SQLStatement, dbOpenSnapshot)
        
        With rst1
            Do While Not .EOF
                If (strReturn <> "") Then
                    strReturn = strReturn & Delimiter
                End If
                If (FieldName <> "") Then
                    strReturn = strReturn & .Fields(FieldName)
                Else
                    strReturn = strReturn & .Fields(0)
                End If
                .MoveNext
            Loop
            .Close
        End With
        
    Exit_Process:
        Set rst1 = Nothing
        Set dbs1 = Nothing
        RecordsToCSV = strReturn
        Exit Function
        
    Err_Process:
        MsgBox Error$
        Resume Exit_Process
        
    End Function
    

    Write your SQL statement as follows:

    SELECT ProjectID, ProjectName, GetProjectNames(ProjectID) As SubNames
    FROM Projects
    ORDER BY ProjectName



    Tuesday, September 22, 2015 5:06 PM
  • I have not had the opportunity to try it yet, but I do intend to. Your page has been vey helpful in the past for other items I looked up.  And I believe one of the many books I purchased have pointed to your website.  I believe this is how I originally was introduced to your website.  Thank you for all the help you never knew you assisted with.
    Tuesday, September 22, 2015 7:38 PM
  • I have not had the opportunity to try it yet, but I do intend to. Your page has been vey helpful in the past for other items I looked up.  And I believe one of the many books I purchased have pointed to your website.  I believe this is how I originally was introduced to your website.  Thank you for all the help you never knew you assisted with.

    Hi. Not a problem. Just let us know if you run into any problems trying to follow our suggestions. Good luck!
    Tuesday, September 22, 2015 10:16 PM
  • Ok, I have been able to attempt to use this.  It seem as though it wants to work; however, I get a Runtime Error:  3464 Data type mismatch in criteria as a response.  I know that these are both in fact Short Text.  Can you assist?
    Thursday, October 8, 2015 12:12 PM
  • Ok, I have been able to attempt to use this.  It seem as though it wants to work; however, I get a Runtime Error:  3464 Data type mismatch in criteria as a response.  I know that these are both in fact Short Text.  Can you assist?

    I have to assume that your problem is in the first procedure I gave to you.  I'm not sure why you have numeric values in a text field, but here's the solution.

    Function GetProjectNames(ProjectID As String) As Variant
        
        Dim varReturn As Variant
        Dim strResult As String
        Dim strSQL As String
        
        varReturn = Null
        
        strSQL = "SELECT SubProjName FROM SubProjects WHERE ProjectID='" & ProjectID & "'"
        strSQL = strSQL & vbCrLf & "ORDER BY SubProjectName"
        
        strResult = RecordsToCSV(strSQL, ", ")
        
        If (strResult <> "") Then
            varReturn = strResult
        End If
        
        GetProjectNames = varReturn
        
    End Function


    Thursday, October 8, 2015 1:45 PM
  • the DB guy, using your code provided to me I previous (SimpleCSV()), I inserted the following into a query, as such:

    
    SimpleCSV("SELECT [Organization]&':  '&[Number] FROM qryCrossReference WHERE [Case]=" & [Name])
    

    I receive an Runtime Error :  3464 Data type mismatch in criteria as a response.  Can you assist?

    Thursday, October 8, 2015 6:40 PM
  • the DB guy, using your code provided to me I previous (SimpleCSV()), I inserted the following into a query, as such:

    
    SimpleCSV("SELECT [Organization]&':  '&[Number] FROM qryCrossReference WHERE [Case]=" & [Name])
    

    I receive an Runtime Error :  3464 Data type mismatch in criteria as a response.  Can you assist?

    Since DB Guy doesn't seem to be available, I will offer my assistance once more.

    Your problem is in two different places, the first is your concatenation of organization and number, your second is as the error message tells you, your criteria.  Again, it appears you are comparing a string in your WHERE clause, which means that you require quotations around the comparison value.  The following should hopefully solve your problem.

    Regards,

    SimpleCSV ("SELECT [Organization] & ':  ' & [Number] FROM qryCrossReference WHERE [Case]='" & [Name] & "'")

    Thursday, October 8, 2015 8:47 PM
  • RunningMannHD, Thank you for the response.  It seems to have worked.  I tested the dataset limited to a single record with success; however, when I tried it with the complete dataset from the query the field is blank.  And suggestions?
    Friday, October 9, 2015 1:35 AM
  • RunningMannHD, Thank you for the response.  It seems to have worked.  I tested the dataset limited to a single record with success; however, when I tried it with the complete dataset from the query the field is blank.  And suggestions?

    Actually, this also returns an empty field with single record.
    Friday, October 9, 2015 1:41 AM
  • This query joins the 2 tables --

    SELECT [Project Table].ProjectID, [Project Table].Name AS ProjName, [Sub Table].Name AS SubProjName

    FROM [Project Table] INNER JOIN [Sub Table] ON [Project Table].ProjectID = [Sub Table].ProjectID;

    This query numbers the records --

    SELECT ProjSubProj.ProjectID, ProjSubProj.ProjName, ProjSubProj.SubProjName, Sum(IIf([XX].[SubProjName]<=[ProjSubProj].[SubProjName],1,0)) AS SubProjNUM

    FROM ProjSubProj, ProjSubProj AS XX

    WHERE (((XX.ProjectID)=[ProjSubProj].[ProjectID]))

    GROUP BY ProjSubProj.ProjectID, ProjSubProj.ProjName, ProjSubProj.SubProjName;

    This crosstab put each in a separate field named as a number --

    TRANSFORM First(SubProjNumbering.[SubProjName]) AS FirstOfSubProjName

    SELECT SubProjNumbering.[ProjectID], SubProjNumbering.[ProjName]

    FROM SubProjNumbering

    GROUP BY SubProjNumbering.[ProjectID], SubProjNumbering.[ProjName]

    PIVOT Numbering_in_group.[Expr1] IN( 1,2,3,4,5);

    This query concatenate the number named fields –

    SELECT Numbering_in_group_Crosstab.DEPT_NAME, IIf([2] Is Null,[1],[1] & ", ") & IIf([3] Is Null,[2],[2] & ", ") & IIf([4] Is Null,[3],[3] & ", ") & IIf([5] Is Null,[4],[4] & ", ") AS My_List
    FROM Numbering_in_group_Crosstab;

    EDITS IN BOLD -- FULLY TESTED


    Build a little, test a little



    Friday, October 9, 2015 3:28 AM
  • RunningMannHD, Thank you for the response.  It seems to have worked.  I tested the dataset limited to a single record with success; however, when I tried it with the complete dataset from the query the field is blank.  And suggestions?


    Actually, this also returns an empty field with single record.

    Test your queries starting with the main one that you're calling the procedure from. 

    Verify your fields and values are as expected.

    Then test the query you are supplying to the procedure.

    Open the query editor and insert your SQL:

    SELECT [Organization] & ':  ' & [Number] FROM qryCrossReference WHERE [Case]="<sample case value>"

    Try testing with and without the WHERE clause.  Verify that [Case] is valid and returning what you expect.

    If that doesn't return records, then test the query "qryCrossReference."  Again, verify your expected fields and their values.



    Friday, October 9, 2015 3:54 PM
  • RunningManHD,

    Thank you for your help.  I went back to this answer and I believe that I have been able to fix the problem thanks to your assistance.

    Monday, January 11, 2016 2:29 PM
  • Hi Novice,

    Sorry I missed your post earlier. Glad to hear you got it all sorted now. Good luck with your project.

    Monday, January 11, 2016 4:22 PM