Concatenate fields from multiple tables - repost

// reposted: to straighten table data //
I am attempting to use Duane Hookom’s concatenate function without any 
success.  I’ve read many threads, relating to Duane’s function, yet none seem 
to fit my situation.

I am working with multiple tables, with a M:M ‘Joining’ table in the middle

Here is an abridged description of my tables and data for illustrative 
purposes:

tbl_Investigator			
InvestigatorID	1	2	3
FirstName	               Bob	Tim	Ted
LastName	               Smith	Johnson	May

tbl_Join			
JoinID                       	1	2	3
InvestigatorID            	1	2	3
ProjectID  	                3	3	3
InvestigatorRoleID      1	3	3

tbl_Project			
ProjectID  	               1	2	3
ProjectTitle               Project1	Project2	Project3

lup_InvestigatorRole			
InvestigatorRoleID	1	2	3
InvestigatorRole	Lead	Co-Lead	Assistant

I would like to know if Duane’s function can concatenate such a scenario 
where I have multiple tables and a M:M relationship connecting them all.

Ideally, I would like to create a query based upon a PROJECT that will 
concatenate FIRSTNAME, LAST NAME and (INVESTIGATOR ROLE) in parentheses.  I 
want the concatenated fields in a single field with a comma separating each 
entry (no carriage returns or tabs).

This data will be merged to a Word document and various reports within my 
Access db.

I’ve tried for 3 days to make this work and I’m starting to repeat failed 
attempts.

Any advice would be greatly appreciated.
Thank you in advance.
Bill
0
Utf
2/6/2008 3:37:02 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2352 Views

Similar Articles

[PageSpeed] 33

Join all the tables into a single query excluding the tbl_Project. Then 
create a query based on tbl_Project and use an expression like:
Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole & 
')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])

-- 
Duane Hookom
Microsoft Access MVP


"BillA" wrote:

> // reposted: to straighten table data //
> I am attempting to use Duane Hookom’s concatenate function without any 
> success.  I’ve read many threads, relating to Duane’s function, yet none seem 
> to fit my situation.
> 
> I am working with multiple tables, with a M:M ‘Joining’ table in the middle
> 
> Here is an abridged description of my tables and data for illustrative 
> purposes:
> 
> tbl_Investigator			
> InvestigatorID	1	2	3
> FirstName	               Bob	Tim	Ted
> LastName	               Smith	Johnson	May
> 
> tbl_Join			
> JoinID                       	1	2	3
> InvestigatorID            	1	2	3
> ProjectID  	                3	3	3
> InvestigatorRoleID      1	3	3
> 
> tbl_Project			
> ProjectID  	               1	2	3
> ProjectTitle               Project1	Project2	Project3
> 
> lup_InvestigatorRole			
> InvestigatorRoleID	1	2	3
> InvestigatorRole	Lead	Co-Lead	Assistant
> 
> I would like to know if Duane’s function can concatenate such a scenario 
> where I have multiple tables and a M:M relationship connecting them all.
> 
> Ideally, I would like to create a query based upon a PROJECT that will 
> concatenate FIRSTNAME, LAST NAME and (INVESTIGATOR ROLE) in parentheses.  I 
> want the concatenated fields in a single field with a comma separating each 
> entry (no carriage returns or tabs).
> 
> This data will be merged to a Word document and various reports within my 
> Access db.
> 
> I’ve tried for 3 days to make this work and I’m starting to repeat failed 
> attempts.
> 
> Any advice would be greatly appreciated.
> Thank you in advance.
> Bill
0
Utf
2/7/2008 5:42:00 AM
Thank you Duane.  Your suggestion worked brilliantly.

Thank you for your time and the concatenation function.

Bill


"Duane Hookom" wrote:

> Join all the tables into a single query excluding the tbl_Project. Then 
> create a query based on tbl_Project and use an expression like:
> Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole & 
> ')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "BillA" wrote:
> 
> > // reposted: to straighten table data //
> > I am attempting to use Duane Hookom’s concatenate function without any 
> > success.  I’ve read many threads, relating to Duane’s function, yet none seem 
> > to fit my situation.
> > 
> > I am working with multiple tables, with a M:M ‘Joining’ table in the middle
> > 
> > Here is an abridged description of my tables and data for illustrative 
> > purposes:
> > 
> > tbl_Investigator			
> > InvestigatorID	1	2	3
> > FirstName	               Bob	Tim	Ted
> > LastName	               Smith	Johnson	May
> > 
> > tbl_Join			
> > JoinID                       	1	2	3
> > InvestigatorID            	1	2	3
> > ProjectID  	                3	3	3
> > InvestigatorRoleID      1	3	3
> > 
> > tbl_Project			
> > ProjectID  	               1	2	3
> > ProjectTitle               Project1	Project2	Project3
> > 
> > lup_InvestigatorRole			
> > InvestigatorRoleID	1	2	3
> > InvestigatorRole	Lead	Co-Lead	Assistant
> > 
> > I would like to know if Duane’s function can concatenate such a scenario 
> > where I have multiple tables and a M:M relationship connecting them all.
> > 
> > Ideally, I would like to create a query based upon a PROJECT that will 
> > concatenate FIRSTNAME, LAST NAME and (INVESTIGATOR ROLE) in parentheses.  I 
> > want the concatenated fields in a single field with a comma separating each 
> > entry (no carriage returns or tabs).
> > 
> > This data will be merged to a Word document and various reports within my 
> > Access db.
> > 
> > I’ve tried for 3 days to make this work and I’m starting to repeat failed 
> > attempts.
> > 
> > Any advice would be greatly appreciated.
> > Thank you in advance.
> > Bill
0
Utf
2/7/2008 8:36:05 PM
Glad to hear it is working for you.

-- 
Duane Hookom
Microsoft Access MVP


"BillA" wrote:

> Thank you Duane.  Your suggestion worked brilliantly.
> 
> Thank you for your time and the concatenation function.
> 
> Bill
> 
> 
> "Duane Hookom" wrote:
> 
> > Join all the tables into a single query excluding the tbl_Project. Then 
> > create a query based on tbl_Project and use an expression like:
> > Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole & 
> > ')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "BillA" wrote:
> > 
> > > // reposted: to straighten table data //
> > > I am attempting to use Duane Hookom’s concatenate function without any 
> > > success.  I’ve read many threads, relating to Duane’s function, yet none seem 
> > > to fit my situation.
> > > 
> > > I am working with multiple tables, with a M:M ‘Joining’ table in the middle
> > > 
> > > Here is an abridged description of my tables and data for illustrative 
> > > purposes:
> > > 
> > > tbl_Investigator			
> > > InvestigatorID	1	2	3
> > > FirstName	               Bob	Tim	Ted
> > > LastName	               Smith	Johnson	May
> > > 
> > > tbl_Join			
> > > JoinID                       	1	2	3
> > > InvestigatorID            	1	2	3
> > > ProjectID  	                3	3	3
> > > InvestigatorRoleID      1	3	3
> > > 
> > > tbl_Project			
> > > ProjectID  	               1	2	3
> > > ProjectTitle               Project1	Project2	Project3
> > > 
> > > lup_InvestigatorRole			
> > > InvestigatorRoleID	1	2	3
> > > InvestigatorRole	Lead	Co-Lead	Assistant
> > > 
> > > I would like to know if Duane’s function can concatenate such a scenario 
> > > where I have multiple tables and a M:M relationship connecting them all.
> > > 
> > > Ideally, I would like to create a query based upon a PROJECT that will 
> > > concatenate FIRSTNAME, LAST NAME and (INVESTIGATOR ROLE) in parentheses.  I 
> > > want the concatenated fields in a single field with a comma separating each 
> > > entry (no carriage returns or tabs).
> > > 
> > > This data will be merged to a Word document and various reports within my 
> > > Access db.
> > > 
> > > I’ve tried for 3 days to make this work and I’m starting to repeat failed 
> > > attempts.
> > > 
> > > Any advice would be greatly appreciated.
> > > Thank you in advance.
> > > Bill
0
Utf
2/7/2008 9:15:01 PM
Reply:

Similar Artilces:

Can not group on fields selected with '*'
Trying to run an aggregate/totals query to group on a particular field but getting message...."Can not group on fields selected with '*' . And when I pick one field in qry all of the fields in the table show up in the View. Is it due to the relations? Thanks On Sun, 24 Jan 2010 20:07:02 -0800, Steve Stad <SteveStad@discussions.microsoft.com> wrote: >Trying to run an aggregate/totals query to group on a particular field but >getting message...."Can not group on fields selected with '*' . And when I >pick one field in qry all of the...

Re: Outlook Problems migrating to Office XP from Office 2000.-- Repost
I just upgraded to Office XP. Outlook, which I use only for tasks, contacts and calendaring, not email, is not working properly or is working differently in several respects from how Outlook 2000 worked. I use Outlook Express as my default email client. 1. Outlook XP has somehow linked to my Groupwise email account. Whenever I start Outlook XP, it immediately accesses the Groupwise server and imports my GW email messages. If I delete the GW messages from Outlooks mail client, they're deleted from the GW server. Question: a. How do I unlink Outlook XP from my GW mail account?...

Table arrays?
Hi all Trying to find a way to produce a list of teams(fixtures) from a table of dates. A1 b c d e f 2 team a team b team c term d 3 team a 00-Jan 01-Jan 07-Jan 14-Jan 4 team b 01-Feb 00-Jan 14-Feb 07-Jan 5 team c 07-Feb 14-Jan 00-Jan 01-Feb 6 team d 14-Feb 07-Feb 01-Jan 00-Jan Ignore 00-Jan Don't know where to start! Match or vlookup? Any pointer would be helpful to start me off. cheers One play to try ... Assume the source table is in Sheet1, in A1:E5 In Sheet2 ------- Put 3 headers in A1:C1, i.e.: Home, Away, Date Put in A2: =OFFSE...

Sales User Define Field Inquiry Zoom
I'm working Great Plains 7.0 What table can I find the Sales User-Defined Fields Inquiry Zoom information? I need the detail information. Thank you, KBrown SOP10106 for the user defined values on specific transactions. SOP40400 to define the available user defined values -- Jim@TurboChef "KBrown" wrote: > I'm working Great Plains 7.0 > What table can I find the Sales User-Defined Fields Inquiry Zoom > information? I need the detail information. > > Thank you, > KBrown Thank you very much "Jim@TurboChef" wrote: > SOP10106 for the user...

Closing Multiple Instances
Hi Everybody Apologies if you have seen this before, I posted it in the Access group which was probably the wrong place Windows XP, Access 2000, Outlook 2000 I am looking for a way to Close All Instances of Microsoft Outlook, with VBA. Both the "Seen One" and the unseen ones that you see when you do a Ctrl Alt Del, and see them all sitting there in the task manager. I would like to have a Command Button that shuts them all. There must be a way to do it because this neat little software product called knockout does just that. http://sunflowerhead.com/software/knockout/ Regard...

Money 2007
Howdy, I have 2 PCs with Money 2007 on them. Can I store the main MNY file on a network share and then access it from both PCs (not at the same time obviously)? Just not sure if everything is stored in that one file or if I'd run into any problems doing it that way. Anyone know for sure? In microsoft.public.money, Mr B wrote: > > >I have 2 PCs with Money 2007 on them. Can I store the main MNY file on a >network share and then access it from both PCs (not at the same time >obviously)? Just not sure if everything is stored in that one file or if I...

Advanced filters in Pivot tables ?
Hello, Is it possible to use some kind of formulas to filter Pivot tables, like with the Auto-filter functionality ? For example, I have a pivot table showing me Customer information, but my customer codes start with a letter, then a numbering. Axxxx, Bxxx etc... Can I filter on this field for 'all customers starting with A ?' without having to tick/untick all records I want to see ? Or, on a date field, all dates between say 01/01/2005 and 15/09/2005 ? Thanks I go back to the original data and insert a new column. Then I create a formula that will help: =if(left(a2,1)="a&q...

Date Time Stamp in Table
I have a field in my table called "Status' can I add another field to my table that gives a date and time to when I changed the field in each record in Status, [Status] being a text field options Active;Finished ......... thanks for any help with this..Bob ...

How can I create a League table based on points scored?
Can anybody help me? I am trying to create a League Table based on 8 people and their tota scores (eg. a fantasy football league). Eg. Player A = 10 points Player B = 13 points Player C = 8 points Player D = 11 points etc., etc. Table should read: Player B with 13 points Player D with 11 points Player A with 10 points Player C with 8 points etc., etc. How do I get Excel to list the players in order of their scores, mos points = top of the league? And when I change the weekly score they might change position depending on their total. Any help on this will be very helpful and appreciated, T...

Query 2 tables, find records where the key matches
I have: Table 1 Name SSN Vendor Table 2 Name SSN Address Table 2 has ~4200 records, Table 1 ~800. I want to pull the 800 records from Table 2 that match the SSNs on Table 1. The tables were joined as a 1-1 by SSN, and both tables use the SSN as the Primary Key. The SSNs are stored as Text with matching input masks and are saved with the dashes in the table, if that's releveant. Thanks for any help anyone can give me- I'll appreciate it. If you have a 1-to-1 relationship then just store it in the same table... Name, SSN, Vendor, Address However, for now: SELECT...

Pivot table field types
Hi, when dragging in data fields into a pivot table, the default field type most often comes up as 'count'. I often have to right click over the field, and change the field setting to sum, and do this individually for each field. Does someone know if there is a way to change the default to 'sum' or to quickly convert all the data fields to sum in my pivot table? This is annoying the hell out of me! Much appreciated, Steve. -- stevehere ------------------------------------------------------------------------ stevehere's Profile: http://www.excelforum.com/member.php?actio...

Shared Sub-form referring to a field in the same sub-form
Hello. I have a subform called "frm_SubformCaseDetail". This sub-form is a sub-form on many Forms, about 20 in total for so many different reasons. I have a problem in the sub-form. One field references another field, example the Sub-Category combobox field narrows down its list based upon the Category combobox field. Since the query that runs the subform needs to be configured to look at the Category field it wants to point to a particular form with the entire path including [Form] [Sub-form] [field]. I don't want to create a seperate subform for each of the 20 F...

insert table into connecting text box w/link
How do I insert a rather large table (single column, many rows) into text boxes which overflow from page to page? You don't... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "display name" <display name@discussions.microsoft.com> wrote in message news:B397C80A-B72D-4D1B-B106-E14E3222A331@microsoft.com... > How do I insert a rather large table (single column, many rows) into text > boxes which overflow from page to page? ...

duplicate names in Pivot Table
The more I use Excel, the more surprises I get! I have a Pivot table based on a 16-column data list, one of the columns being "End Uses," say, office machines, laptops, FAX machines, etc., a total of 15 different end uses. Each End use occurs several times in the column. For some reason, two of these end uses, though spelled identically, appear twice on the drop-down list of the table. Any idea how that might have happened? Probably one of the items has a space character at the end, and the other doesn't. K. Georgiadis wrote: > The more I use Excel, the more surpri...

ReportstoPDF
I've been searching for an answer all day but havn't had any luck. I've built a VBA application that generates about 80 pages per set, and there are 9 sets in total. Currently i'm using Mr. Leban's ReportToPDF code that helps to automatically generate the reports to PDF format. But now i'm left with 80 individual PDF files per Set. Is there a way to put all 80 reports into one PDF file via VBA code? Any help would be greatly appreciated. Thanks, Rich What about printing to PDF Factory Pro? I've figured it out. I went to the adobe website and downloaded t...

pivot table
hi Though I have been using Excel for quite a while. I just did my first pivot table today. I need a report based on specific date (Actually it is every Sunday). The date I have entered are Nov 1, 2009 and Nov 8, 2009) in the Excel but when I used date as the page report filter. The date filter is <Nov 1 > Nov 9 with many dates in between. How do I make it to show only Nov 1, 2009 and Nov 8, 2009. I really very happy because pivot table really useful tool except for my problem re date. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/exce...

Updating Pivot Tables Dynamically
I'm currently looking to implement a solution to automate the process of updating 5 pivot tables (one after another) on the same worksheet in an Excel workbook. Within the workbook there is a worksheet which is used as the data source for the pivot tables. If the number of items in the data source does not change the pivot table updates without any problems. However if the number of items in the data source increases the pivot tables start overlapping which causes errors. My guess here is that you can’t place pivot tables in a worksheet and have them adjust their size...

What happened to "pivot table has been changed" message?
In excel 97, when the source data for a pivot table was changed, then the pivot table refreshed, an information message saying "pivot table has been changed" appeared. I am now using excel 2002 and when I refresh tables, I am not receiving the message. Is this an "upgrade" or can I turn this function on somewhere? Regards, Amanda ...

Link current query to a table?
I'm trying to create a cost estimate form, and I'm working on one underlying query to power it. Its based off a lot number. A certain digit in our lot number indicates the blend of the product. I have a table of blend codes and descriptions based on that digit. I have a column to calculate the blend code from the lot number but I am having no luck getting the description into either the query or the form. I have tried a Dlookup but I seem to have trouble getting it to refer back to the query that it is in. I know I could create another table of Lots and their associ...

Multiple Dictionaries in Right-Click 'Add To Dictionary'
It would be really useful to have a drop-down list of current dictionaries when you right-click a word to add to dictionary. I work on multiple diverse projects - from programming to fiction writing - so, unless I can be bothered to go into options every time I open a document to set the current default dictionary, I have to add all words to one dictionary. So, for example, I will end up adding the name of a fictional character to the same dictionary I use for programming documents, which is silly. Alternatively, it would be good if the current default dictionary could be se...

30 tables in a form?
How come when i tried to put all my tables into a form, Access doesn't let me do it and gave me an error? You need to provide more information. A form may be based on a query comprising several tables, but it is unclear what you mean when you say you "tried to put all my tables into a form". In what way exactly are you attempting to do that, and what is the error message? What do you hope to accomplish? <emerlita@shaw.ca> wrote in message news:1176302831.519449.88430@d57g2000hsg.googlegroups.com... > How come when i tried to put all my tables into a form, Acce...

Multiple iexplorer.exe on task manager
Everyone is having the same problem. We all download the same "virus", "spyware" etc... Maybe it is in the MS updates...hmm... I have the same problem and here is how it went. Multiple iexplorer.exe, close Iexplorer and 1 to 6 stay in task manager. Read post after post, ran virus scans after virus scans, ran spyware scans and more spyware scans. I gave up! Had a brand new hard drive that I have been wanting to put in my laptop so I decide to redo os on laptop. Loaded OS, Laptop Drivers, and MS updates and that is it, nothing eles. guess what?? as I'm typing...

Combine Multiple Worksheets into One with a Drop Down Box
Hello, I was hoping someone could help me. I have a "master" template set up and each of my sales reps goes in and fills out the required information. I was wondering if there was a way to "combine" all of them into the master, so all I would have to do to look through each persons sheet is click on a drop down box on the master sheet, click on the person's name, and all of their information would come up. Any help will be appreciated. Thanks. JK I'm not sure what you have. Do you have numerous workbooks, one for each sales rep, and you want t...

Combining multiple files into one work sheet
Good afternoon, I have multiple files single worksheet files (30), each in it's own directory that I would like to have combined into a single workbook. Each file name is unique, however the midships of each file contains the date the file was created "C:\XVG001\XVG001_20090713_Reviewed.xls". Is there any way I can create a function or macro that will pull these files from their respective directories and combine these sheets automatically into one workbook? Thanks Try this add-in http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/t...

Print multiple excel charts within one sheet to fit to a page lik.
I have set up 5 charts within one excel sheet and need to print them on one page arranged nice and neat the way that PPT does. Currently I am not able to find a solution to do this in Excel. It would be much more efficient to do this in Excel Open and close Print Preview. Now you will see dotted lines indicating your page breaks. That should help you when positioning your charts. Drag the charts with your mouse. The cursor won't change, or at least it doesn't for me; but you can still drag them. "lal" wrote: > I have set up 5 charts within one excel sheet and n...