Solved

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

Posted on 2008-06-19
10
329 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
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: 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
 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 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