Avatar of MCaliebe
MCaliebe
Flag for United States of America asked on

Best query solution for a form with a one to many results

Hi everyone,

I have a table which houses a number of cross reference entries.  I am building a form to display this data.  My table includes item_nr, vedor, v_item_nr.  I have approximately 10 vendors to display.

For each item number, I need to display the cross reference information as

Header
Selection, Vendor1, Vendor2, Vendor3, Vendor4, ect...
Detail
Item_nr, V_item_nr, V_item_nr, V_item_nr, V_item_nr

In cases where one Vendor has multiple items, I assume I'd have more then one record in the form.

Would I need to build 10 different queries and combine them based on my Item_nr selection or is there a better way to handle this?

My current form uses a query that takes the data from ten different vendor tables, however I was told this was an inefficent way of handling the data.  I normalized it to one table, however now I am uncertain how to best capture the data.

I attached a screen shot of my current form.   Thanks for whatever help is offered.
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

@MCaliebe

screenshot is missing.

Normally, the best way to display this information would be vertically in a subform, rather than trying to do it in a query.  This way, you could have any number of vendors and V_item_nr values.
MCaliebe

ASKER
Screen shot attached
ScreenHunter-01-Dec.-28-11.52.jpg
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MCaliebe

ASKER
Can you direct me to an example of this type of form.  I can't wrap my head around the design.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
MCaliebe

ASKER
I got it.   I figured out how I can do this with a simple query and a form in Data Sheet or Continuous view.  I believe we make look for something to be so difficult that we overlook the obvious.
Dale Fye

glad to help.