Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query To pull all related data

Posted on 2014-02-07
12
Medium Priority
?
390 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

636 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