Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-19
10
Medium Priority
?
337 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

772 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