Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
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.
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.
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.
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
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total?
It takes a little, but - when done - it is extremely easy to implement.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll
608 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.