Relationships in Queries

[Guest]

Could someone share they're thoughts about how to properly relate tables in a
Query and what effect that has on overall table relationships in the
database? Thank You.

 

[Paul Overway]

In queries, they are known as joins. They have no relationship to
relationships other than the fact that if you have established a
relationship between tables a join will be created automatically when you
design a query using those tables.

As for how to properly relate tables, you'd select Tools|Relationships,
showing tables, and then establish the relationship by dragging one related
field to another. You'll be prompted about referential integrity and
whether you want cascade updates/deletes.

 

[Wayne Morgan]

The relationships you place in the queries should have no effect elsewhere
in the database except on those items that use the query. As far as how to
properly relate them in the query goes, use what is needed to get the job
done. In other words, it depends on what you're trying to do.

 

[Joseph Meehan]

Could someone share they're thoughts about how to properly relate
tables in a Query and what effect that has on overall table
relationships in the database? Thank You.

It makes no difference to the rest of the database. The proper
relationship depends entirely on the reason you are creating the query and
their natural relationships.

 

[Guest]

Actually, your comment is a little backward. You should be creating
relationships using the Relationships Window to identify how each of the
tables relate to one another within the database. This information is then
used automatically when you create a query.

It's a matter of identifying the primary key / foreign key relationship.

As an example; if you have an order entry database you could have a table
for the order header information, a table for the order detail information
and a table for the customer information.

tblCustomer could contain CustomerNumber, CustomerName, ShippingAddress,
BillingAddress etc.
tblOrderHeader could contain OrderNumber, CustomerNumber, DueDate,
PaymentTerms etc.
tblOrderDetail could contain OrderNumber, OrderQuantity, PartNumber etc.

Each table is connected by a common field. OrderHeader and Customer are
related by CustomerNumber. OrderHeader and OrderDetail are related by
OrderNumber.

 

[Jeff Boyce]

Maybe it's only a matter of semantics...

You can use the Relationships window to relate tables in Access. You
specify which field(s) are in Primary-Foreign key relationships.

You can join tables together on ANY field(s) in Access queries. It only
makes sense if the fields hold common data. AFAIK, the joins you create in
a query have NO impact on table relationships.

 

[Guest]

Thank You All..."Joins" was what I was looking for... that seems to
differentiate things for me....and You are right..I am thinking backward...
I'll get it some day.