How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.
Solved
Best query solution for a form with a one to many results
Posted on 2012-12-28
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
6 Comments
Active 1 day ago
@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.
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.
Active 6 days ago
Screen shot attached
ScreenHunter-01-Dec.-28-11.52.jpg
ScreenHunter-01-Dec.-28-11.52.jpg
Active 1 day ago
As I said, it generally makes more sense to display this as a subform (or as a continuous form popup) rather than trying to go horizontal like this. For one thing, as you add new vendors, you will have to add columns to the form or reports, but if you go vertically, with columns for Vendor, v_item_nr, and price you can have as few (or many) vendors as you want, and will only display those vendors which sell that item.
Active 6 days ago
Can you direct me to an example of this type of form. I can't wrap my head around the design.
Active 6 days ago
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.
Featured Post
Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
Suggested Courses
Course of the Month9 days, 14 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.