Relationships for queries

Usually, when I design a query (and most often when I design a query
based on other queries) , I can drag a field from one table to another
to define a relationship between the tables.

I have just noticed that I can include queries in the Tools -
Relationships diagram.

If I do this, am I hard-coding relationships between queries? Is there
an advantage/disadvantage to doing so?

Thanks

"MikeB" wrote in message
...
Usually, when I design a query (and most often when I design a query
based on other queries) , I can drag a field from one table to another
to define a relationship between the tables.

Yes and you're talking about designing queries in the query builder. When
you design queries in the query builder you will often drop in additional
tables (or queries) into the query builder. when you build relationships in
your query builder, you're not affecting the design or system of
relationships in the table design mode, there really rather quite separate.
furthermore since there's no ability to enforce any kind of reference one
tech critea, what would be the long-term or short term effects of building a
query with a relationship to another query, the answers absolutely nothing
in terms of how your databaseis going to function at the table level.


If I do this, am I hard-coding relationships between queries? Is there
an advantage/disadvantage to doing so?


I guess you'd have to define what you mean by hard coding, but the answer is
since drawing this diagram can't can't do anything, then about the only
advantage I see is for documentation purposes.

If you look closely you'll notice that the enforce refferential integrity
options are not available.

So, keep in mind if you bring up the relationships window and draw some
relationship lines between tables and don't enforce anything, then they
don't have any real effect on the operation of your application do they?

Building a relationship is only going to affect the operation of your data
base **WHEN** you use that relationship to enforce some type of behavior or
"rules" on your tables. Since you're not enforcing any type of rules
(casecade update, cascade delete, or enforced integrary), then you're not
really doing anything that affects anything.

You could clutter up the relationships window with some queries for
documentation purposes, but for the most part it don't see the need. Other
possible reason is you might want to display some tables but not all the
fields (so, using a query in place of the table would restrict some of those
fields shown for your diagrams).

Unless you're forcing some rules for integrity etc, the drawing of a line
does really very little.

I going to include (after my signature) a previous post of mine about how
you can use relationships in your database to document what the original
developers intentions were at the time of design.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada


========================================

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This is obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKal...Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

Hi Mike,

If I do this, am I hard-coding relationships between queries?

No. At most, a new query that uses a query that you've previously included
in the Tools | Relationships view will simply inherit the join. However, the
JET database engine can only enforce referential integrity (RI) between
tables in the same database; it cannot enforce RI for a join between a query
and a table, or a join between two queries, that is defined in the
relationships view. Personally, I find no compelling reason to include a
query in the relationships view.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"MikeB" wrote:

Usually, when I design a query (and most often when I design a query
based on other queries) , I can drag a field from one table to another
to define a relationship between the tables.

I have just noticed that I can include queries in the Tools -
Relationships diagram.

If I do this, am I hard-coding relationships between queries? Is there
an advantage/disadvantage to doing so?

Thanks

Ah...I bumped send key here!!1

Lets try this again!!
\\===


"MikeB" wrote in message
...
Usually, when I design a query (and most often when I design a query
based on other queries) , I can drag a field from one table to another
to define a relationship between the tables.

Yes and you're talking about designing queries in the query builder. When
you design queries in the query builder you will often drop in additional
tables (or queries) into the query builder.

When you build relationships in
your query builder, you're not affecting the design or system of
relationships in the table design mode. They really are rather quite
separate.

Furthermore since there's no ability to enforce any kind of integray,
the long-term or short term effects of building a
query with a relationship to another query means very little in how
your database is going to function at the table level.


If I do this, am I hard-coding relationships between queries? Is there
an advantage/disadvantage to doing so?


I guess you'd have to define what you mean by hard coding, but the answer is
since drawing this diagram does little, the the only
advantage I see is for documentation purposes.

If you look closely you'll notice that the enforce refferential integrity
options are not available.

So, keep in mind if you bring up the relationships window and draw some
relationship lines between tables and don't enforce anything, then they
don't have any real effect on the operation of your application do they?

Building a relationship is only going to affect the operation of your data
base **WHEN** you use that relationship to enforce some type of behavior or
"rules" on your tables. Since you're not enforcing any type of rules
(casecade update, cascade delete, or enforced integrary), then you're not
really doing anything that affects anything.

You could clutter up the relationships window with some queries for
documentation purposes, but for the most part it don't see the need. Other
possible reason is you might want to display some tables but not all the
fields (so, using a query in place of the table would restrict some of those
fields shown for your diagrams).

I going to include (after my signature) a previous post of mine about how
you can use relationships in your database to document what the original
developers intentions were at the time of design.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada


========================================

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This is obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKal...Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.