Solved

Query To pull all related data

Posted on 2014-02-07
12
369 Views
Last Modified: 2014-02-09
Hello
There is DB that I just created.

Assumption -
Each nation has several states but each state will never be owned by several nations
Each state has several districts but each district cannot belong to several states
Each item can be sold to several districts and each district can have several items

How do I construct a form that that can fill in the order table keeping in mind the this relationship?

Thanks
DataB.accdb
0
Comment
Question by:Rayne
[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
  • 10
  • 2
12 Comments
 

Author Comment

by:Rayne
ID: 39842755
the orders form....that i am after
0
 

Author Comment

by:Rayne
ID: 39842802
orderID      ItemID      Nation      State      District
1      i89      USA      Colorado      North East
0
 

Author Comment

by:Rayne
ID: 39842808
The order table will have the above attributes or columns….
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rayne
ID: 39843275
I just installed the northwind database…can someone be kind enough to direct me to a sub form (within the  northwind DB) that  might help me get going in this direction?

http://download.cnet.com/Access-2000-Tutorial-Northwind-Traders-Sample-Database/3000-2251_4-10742880.html
0
 

Author Comment

by:Rayne
ID: 39843456
Please check query 2  - is this is the optimal query way to represent all the components of the Orders table? Or I am doing something inefficient?
0
 

Author Comment

by:Rayne
ID: 39843458
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39843868
The query and concept looks right.

What are you trying to get to?
0
 

Author Comment

by:Rayne
ID: 39844726
Hello Jim,

Thank you for confirming :)

Given the structure for my data - One thing if its possible to indicate - do you think just one form is enough to fill in the fields for these? or sequence of forms in this order?


(1) I need to create a form to fill (Nation  +    State  +    District)  first ,
(2) then create another form for filling Item and (3) create  another form for filling orders?

Does that make sense or I am thinking wrong?

R
0
 

Author Comment

by:Rayne
ID: 39844734
Goal - users should only have forms to fill in the different table entities in the DB. They should not be able to use any other means to fill in these tables, only via forms
So my thing - i need to understand (1) what forms do i need create to fill in all tables and (2) how to sub form one form on the main form if possible? - for the ORDERs table
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39844762
The concept is that you should have the main form with a customer ID. If it is a new customer then you need to decide to get the order first or after you get the shipping info.

Then the subform should be linked on the customer ID and a separate sub form for the order items. And because a customer can have multiple shipping vs the billing address. I know in my case I have shipped gifts via Amazon to my mother, father, sister, my work address, and others but it bills back to me and my address.

Then there is another subset in building the address table. I know both Canada and the US have postal or ZIP codes. I think other countries have them as well. Then there is the further subset in many US cities that has multiple ZIP codes.

So when the end user/clerk is entering the shipping info they need to be aware of billing and shipping address.

I would build the DB that there is a user profile table, in which the user can pick their default country. Then the subform would be based of the default profile but an option (combo box) at the top of the form to change the country.

Then the name, multiple address lines are pretty much free form. The state and other type boxes are limited to what is in the combo boxes. The postal/ZIP issue gets a bit more complicated. I don't know of any free resource to get the database of ZIP codes. I know they exist though.  I've been on both websites and calls where the operator wants to know your street address "4321 Oak Street" and your ZIP "45424" and then they read back "You're in Wimington."

I hope this gives you an insight to the development conversations that can go on.
0
 

Author Comment

by:Rayne
ID: 39844993
Hello Jim,
I can't thank you enough for sharing this gold idea :) and offering me a wake up call (i was sleeping :(

Your help really gave me a good idea of what I need to think about and stress on. Thank you Sire for your kindness, greatly appreciate it

Respect
R
0
 

Author Comment

by:Rayne
ID: 39845184
Thank you again Jim :), this is a eye opener for a newbie, thank you
0

Featured Post

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)

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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 …

739 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