Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
@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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial