Queries in Many2Many Relations - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Queries in Many2Many Relations

Kent Jamison

3 years ago

Can you use queries in many-to-many relationships? I'm asking because I have a NameQ that concatenates all the name fields (FirstName, LastName, Rank, MiddleInitial, etc.) together in various orders. I'm wonder if I can put that on one side of a junction box and personnel table on the other side (with position details). Possible? Or does both sides of the junction box have to be tables?
Thoughts?

	Scott Axton @Reply 3 years ago
Kent - The answer is No and Yes. I'll see if I can explain. Typically you are relating Tables together using the IDs (AutoNumbers). The AutoNumber fields are how Access knows the relationship between the Tables.

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago
That being said you can create a query to use in another query to vary how the info is displayed.

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago
You can then use that query to display the info how you want. Bring in the fields that you want to display.

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago
Note that the CustomerLFQ is still based on the CustomerT so the ID is being stored NOT how the information from the CustomerT is displayed. I hope that makes sense. If I just confused the heck out of things ask me questions.

	Scott Axton @Reply 3 years ago
Here are a couple of links to show you how to change how that information is displayed: Multi Combo One Field Change Combo Sort

	Kent Jamison OP @Reply 3 years ago
Thanks Scott. I think I'm tracking. My NameQ is just like the one you show here. I stole the idea from Mr. Rost. ;-P One question. In the last screenshot, is the CustomerID the same as in CustomerT? I haven't watched those two videos, but I'll do so. I'll let you know if I have any questions about them. Cheers! Kent J.

	Kent Jamison OP @Reply 3 years ago
Videos are straight forward, now that I know a little SQL and VBA. :-p No questions. Thanks.

	Scott Axton @Reply 3 years ago
Kent - Yes, since the CustomerLFQ is based off of the CustomerT, the ID is from there. I didn't realize that I cut off the ID in the second screen shot. Here is a better picture:

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago
I'm glad that helped you out. Sometimes it is the little nuances that are hard to pick up. Thank you for your service! Just curious. Where are you stationed?

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

Next Unseen

New Feature: Comment Live View