Solved

Query To pull all related data

Posted on 2014-02-07
12
363 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
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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 …

790 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