Ask Your Question
0

Help to create a query and query practical use with one-to-many relationships. [closed]

asked 2014-01-09 02:56:20 +0200

mrmister gravatar image

updated 2014-01-09 21:18:42 +0200

bencomp gravatar image

Hello I am new using the relationships and I have a database of orders and also of customers.

A customer can have many orders, so here we have a clear case of 1 to n (many) relationship.

The best I can do is attaching screenshots and also my database, so you can see the structure.

This is the CUSTOMERS table

CUSTOMERS TABLE

This is the ORDER table

ORDERS TABLE

Here you have the data inside both tables...

image description

image description

Here is how I did the relationship

RELATIONSHIP

Here is the database file, you can download it and make tests with it.

http://netload.in/dateixryCH8uMty/NewDatabase8.odb.htm

My question is... now I have the relationship done... what may I do with this, when creating queries?

For example, in the query design... in the grid, how may I drag and drop fields from Orders table and get the proper relationship with the Customers table? My problem is I know how to create the relationship, but now I don't know what practical use can get with it, when doing a query?

Would you help me with this please? If you could download my Database file and do some queries for me, I could run the queries and see the results, so with a practical example I would understand this better.

Thanks

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-18 14:36:18.291645

1 Answer

Sort by » oldest newest most voted
0

answered 2014-01-09 04:19:43 +0200

frofa gravatar image

updated 2014-01-16 21:35:23 +0200

I think this is a pretty good answer to your question.

The relationships created in Base's RELATIONS DESIGN window are really just CONSTRAINTS to maintain data integrity. Such constraints are not essential to getting useful information out of related tables which require the use SQL JOINS. (The join operation just allows data from more than a single table, especially your 1:n pair of related tables, to be listed in a orderly manner.)

So, in your case, a QUERY using a JOIN would be something like...

SELECT * FROM "Customers" LEFT JOIN "Orders" ON "Customers"."ID" = "Orders"."CustomerID"

Replace the * with the list of columns you want to appear in your query list.

See this useful example of using FORM-BASED filtering via a sub-form displaying data from a related table. Note the way the PKs and FKs work.

edit flag offensive delete link more

Comments

I am sorry, but don't understand you. I have seen a videotutorial from Access and they say relationships have a big impact in the retrieved data from the queries. In this case, even if CustomerID from the order, is Integer, it should retrieve data from Customers table, showing the company name. Here you have a full videotutorial showing how to do this in Access. http://office.microsoft.com/en-us/access-help/video-building-queries-with-the-query-designer-VA104149846.aspx?CTT=1

mrmister gravatar imagemrmister ( 2014-01-09 05:16:24 +0200 )edit

I am planning to do the same quality videotutorials for Base, but relationships I am sure, have practical uses, not just maintaining data integrity. I have seen how to do that in Access and I have seen examples, I need the same in Base. I still need a practical application of this, and some examples of relationships and queries... sorry.

mrmister gravatar imagemrmister ( 2014-01-09 05:19:39 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-01-09 02:56:20 +0200

Seen: 3,482 times

Last updated: Jan 16 '14