With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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 today
@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 today
Screen shot attached
ScreenHunter-01-Dec.-28-11.52.jpg
ScreenHunter-01-Dec.-28-11.52.jpg
Active today
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 today
Can you direct me to an example of this type of form. I can't wrap my head around the design.
Active today
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
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 11 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.