Solved

try to concatenate 2 or more fields from differant tables in access 2007

Posted on 2008-06-19
10
328 Views
Last Modified: 2013-11-28
I am trying to concatenate 2 fields from different tables in Access 2007.

I have few constraints.....  first I cannot change the existing vba code second, I cannot use the group by as the person that ask for the report does not like to display the fields vertically, but he wnat them in a single line... so...

I found in the existing database a module called concatenate(I attached the code) and I am trying to use in this way

Concatenate("SELECT Project_Applicants.Applicant_Name, Projects.Principal_Investigator_Name FROM Projects INNER JOIN Project_Applicants ON Projects.ID =" & [Project_Applicants.Project_ID] & "" , ",")

BUT i GET A runtime error..... JOIN EXPRESSION NOT SUPPORTED

I am not an expert on SQL and I cannot spot my mistake.

the goal here is to show on a single line the Applicants names(they sometime are more than one) and the Principal investigator name.
I also have to deal with empty fields in the above query.

I am using Access 2007
MODULE CONCATENATE
 

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") _

        As String

    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

Open in new window

0
Comment
Question by:brillox
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21820053
Concatenate("SELECT Project_Applicants.Applicant_Name, Projects.Principal_Investigator_Name FROM Projects INNER JOIN Project_Applicants ON Projects.ID=Project_Applicants.Projects.ID" & "", ",")

try this
0
 

Author Comment

by:brillox
ID: 21820566
I get "no value given for one or more required parameter
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21820661
Concatenate("SELECT Project_Applicants.Applicant_Name, Projects.Principal_Investigator_Name FROM Projects INNER JOIN Project_Applicants ON Projects.ID=Project_Applicants.Projects.ID" , ",")
0
 

Author Comment

by:brillox
ID: 21821513
Maybe I am doing something wrong here..

I am copying and pasting the above code into teh SQL view of the query, but I get the same error
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 21821664
Concatenate("SELECT Project_Applicants.Applicant_Name, Projects.Principal_Investigator_Name FROM Projects INNER JOIN Project_Applicants ON Projects.ID=Project_Applicants.ID" , ",")


 Projects.ID=Project_Applicants.ID

In this Check wwich field matching for projectid in Project_Applicants table

if its ID then   Projects.ID=Project_Applicants.ID

if its ProjectID then Projects.ID=Project_Applicants.ProjectID
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:brillox
ID: 21828809
it works.....

perfect
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21828835
glad to knoe it works :)

0
 

Author Comment

by:brillox
ID: 21829018
I realised that my knoweldge of SQL is very basic.. so I will start right away to improve it... particuralry on sub queries and Unions.

Thanks for your help
0
 

Author Comment

by:brillox
ID: 21829023
Did this site change the way to assign ponts ?? "Automated request for review ??

hope I assigned the points to you pratima
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21829377
as per my knoweldge  you need to just accept the solution
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now