Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   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 
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

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
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
Can you post a screenshot of your Query in design view?
It would also be helpful to see the SQL statement.
Daniel Schuster

Daniel Schuster
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
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
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
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
If you're not seeing any results it is probably the Join Type.
See this video: Outer Joins
Daniel Schuster
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
NO NO NO!

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
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.
 

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

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/27/2024 6:18:20 AM. PLT: 1s