Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Combining Names < Volume | Close Form >
Back to Combining Names    Comments List
Names From Another Table Upload Images   Link   Email  
Daniel Schuster       
2 years ago
How do you combine names that are pulled in from another table? I want to list the MPT name in a report, but sometimes there is more than one MPT at a performance, in which case I want it to read "MPT 1 + MPT 2". The report is getting its data from MissionT, so I figured I could make a Query that uses this 'Combining Names' concept - but it's only pulling in the MPTIDs, not the MPTName. I'm having trouble wrapping my head around all this. Can I somehow use a DLookup function in the report to concatenate the MPT names AND combine the names at the same time. Can I concatenate a nested IIF function?
Scott Axton            
2 years ago

Daniel what you "see" in your form is different than what is stored.  You are correct about using a query to concatenate the  two fields visually.  
FirstName & " " & LastName     for instance.

You still want to store just the id, pointing to the records, for relationship purposes.

Check out the Concatenation video if you haven't already.  You might also want to revisit the Relationships video
Daniel Schuster       
2 years ago
As I understand it, in order to get the names to populate instead of the numbers, I have to relate the table with the numbers to the table with the names. Done. However, the query yells at me when I try to include the related table that stores the names. It says 'The specified field 'MPTID' could refer to more than one table listed in the FROM clause of your SQL statement'. When I'm running the IIF function in my concatenation field, can I specify which table to pull from? When I try to specify the Table under the concatenation field, it doesn't like my SQL statement anymore, saying there are too many ')'s present, which there aren't.
Kevin Robertson            
2 years ago
Can you post a screenshot of your Query in design view?
It would also be helpful to see the SQL statement.
Daniel Schuster       
2 years ago

Daniel Schuster       
2 years ago
2MPT: Iff(ISNull([2ndaryMPTID]),[MPTID],[MPTID]&" + " & [2ndaryMPTID])

I want it to replace the ID numbers with names, but the traditional method of relating the table with the names in it has the same 'MPTID' field in it so it doesn't know which table I'm referring to. Should I change one of the names so they aren't the same?
Kevin Robertson            
2 years ago
You are using [FieldName],[FieldName]. It should be [TableName].[FieldName]
Also you are using a comma instead of a period. I can't make out if it is the same in the image.

2MPT: Iff(ISNull([2ndaryMPTID]), [MissionT].[MPTID] & " + " & [2ndaryMPTID])
Daniel Schuster       
2 years ago
Ok, that fixed that problem! Thanks. But it's still only giving me numbers, not names. Shouldn't it be giving me the name from the linked table? Also, both MPTID and 2ndaryMPTID pull from the same table. When I try to make a relationship from MPTID in MPTsT to both MPTID and 2ndaryMPTID in MissionT, it returns no results.
Kevin Robertson            
2 years ago
It's returning the IDs because that is what you are telling it to return in your IIF statement. Change the IDs to the field that contains the names you want returned.
Kevin Robertson            
2 years ago
If you're not seeing any results it is probably the Join Type.
See this video: Outer Joins
Daniel Schuster       
2 years ago
No, it's working. I just keep putting the ID in there instead of the name. I really appreciate your help. Getting the syntax correct with the concatenation was what I needed. Thanks so much!!
Daniel Schuster       
2 years ago

It's pulling in the same name twice. There needs to be a way to differentiate between MPTID and 2ndaryMPTID when replacing the numbers with the related name. Should I just duplicate my MPT field in the MPTsT to make a 2ndaryMPT so it's pulling from a unique location? That sounds like the simplest solution, but Richard might kick me off out of the program if I duplicate data like that ;)
Scott Axton            
2 years ago
First - the best way is to do a screen capture not take a picture of your screen.  Much easier and more clear for us to see.
Image Uploads

Snip & Sketch

Did you watch the Concatenation video I linked above?  That shows how to display the name while still storing the ID.  It is literally what columns you have in the combo box.

You could also qualify the table that you want your query to use the ID from - tell Access which to use - by putting in the [TableName]. in front of the ID.  That way Access wont be confused - which is waht that error message is telling you.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Combining Names.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/15/2024 8:38:22 AM. PLT: 1s