Concatenate of One field Many Records 1

[WebGodiva]

Well, I've been able to concatenate the fields of my one query into one field but still can not bring the result into one field.

I need major help. Here is what i have.

my original query was to concatenate all fields from one employees record into one field. I finally got that to work and the result looks like this:

Professional Engineer: NC (#99999, 6/30/2007) 2000

This comes from five (5) fields in one record.

I received a lot of assistance with this part this week and i appreciate it, I was finally able to make it work.

Now, what I now need to do is take all the records for that one employee (could be from 1 to 50 max) and place them in one field for the query to use as a merge field.

In other words, end result should look like this:

Emp_No:100

ConcatRegistrat would be:
Professional Engineer: NC (#99999, 6/30/2007)2000 - FL (#99999, 6/30/2007)2004 - SC (#99999, 6/30/2007)2001
- TX (#99999, 6/30/2007)1999

So, I would end up with two fields and one record: Emp_No and ConcatRegistrat (both fields will be included in another query at the end and then merged with Word)

My select query looks like this:

SELECT Emp_No, Concatenate("SELECT JoinedReg1 FROM qryCombineRegis  
WHERE Emp_No =" & qryCombineRegis.Emp_No) AS CombineRegistrat
FROM qryCombineRegis;

I keep getting that there is an ambiguous name in the code from Contactenate down to the end but I can't figure out what it is refering to. The original code for qryCombineRegis (where i'm pulling the already concatenated fields is:

SELECT DISTINCT qryEngRegistrationsWTitle.Emp_No, [tblEngCertID].[CertificationTitle] & Chr(58) & Space(2) & [tblEngRegist].[StateIssued] & Space(1) & Chr(40) & Chr(35) & [tblEngRegist].[RegNumber] & Chr(44) & Space(1) & [tblEngRegist].[RegExpires] & Chr(41) & Space(2) & [tblEngRegist].[FirstIssued] AS JoinedReg1
FROM qryEngRegistrationsWTitle INNER JOIN tblEngCertID ON qryEngRegistrationsWTitle.CertIDNo = tblEngCertID.CertIDNo;

This query comes from two (2) separate sources, tblEngCertID and qryEngRegistrationsWtitle. The primary key is the CertID for this query.

I hope someone will be able to assist with this. I have been very fortunate to have a lot of help with getting the above to work correctly and now I'm just one step from having it complete and can't figure it out.

Any help would be appreciated.










"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[dhookom]

For review, what happens when you open the debug window (press Ctrl+G) and enter:

? Concatenate("SELECT JoinedReg1 FROM qryCombineRegis WHERE Emp_No =100")

Since this question has lingered for quite a while, I would consider reviewing your mdb. You would need to contact me directly to arrange this so my email program doesn't delete your mail with attachment.

If you want, my email address it duane AT hookom DOT net.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WebGodiva]

I get that Concatenate is the ambiguous name?

I ran the compiler and no errors show on the module - I'm not quite sure what is happening.



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[WebGodiva]

Thanks for your support - you were correct that there was another function utilizing the concatenate name and i was able to easily change it which corrected the issue.

again, many thanks.





"Unless you try to do something beyond what you have already mastered, you will never grow." Ralph Waldo Emerson