Solved

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

Posted on 2012-12-28
6
261 Views
Last Modified: 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.
0
Comment
Question by:MCaliebe
  • 3
  • 3
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38727663
@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.
0
 

Author Comment

by:MCaliebe
ID: 38727911
Screen shot attached
ScreenHunter-01-Dec.-28-11.52.jpg
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 38727932
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:MCaliebe
ID: 38727964
Can you direct me to an example of this type of form.  I can't wrap my head around the design.
0
 

Author Closing Comment

by:MCaliebe
ID: 38727983
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38727987
glad to help.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now