Solved

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

Posted on 2008-06-19
10
327 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now