Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-19
10
Medium Priority
?
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

660 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