Solved

Query To pull all related data

Posted on 2014-02-07
12
373 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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