Help to create a query and query practical use with one-to-many relationships

[mrmister]

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

This is the ORDER table

Here you have the data inside both tables…

Here is how I did the 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

[frofa]

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.

[mrmister]

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.

Video: Building queries with the Query Designer

[mrmister]

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.