Solved

Query To pull all related data

Posted on 2014-02-07
12
365 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Technology Partners: 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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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 …

735 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