Subform Question

[John V]

I have seen two authoritative examples of a form/subform created using query
fields. The same form/subform functionality can be achieved by using fields
from multiple tables (the ones used to create the query). Or so it seems.

So, I'm wondering why one would go to the trouble of working from a query
instead of multiple table fields.

Any insights appreciated.

John V.

 

[Pat Hartman\(MVP\)]

Good practice dictates that each form/report should be based on its own
query. The query can join the main table to any lookup tables; it can
create calculated fields; it can include selection criteria; it can order
the resultset; etc.

In a properly normalized database, it is very likely that at least one
lookup value is required and so it is rare that you would ever be able to
base a form or a report directly on a table in any event.

 

[John V]

Thanks Pat, I suppose my newbieness is showing.

Follow-up question: I ordinarily use junction tables for many-to-many
relationships. I was told an equally effective (maybe they said 'better';
can't recall) method was to use a query instead to create the relation.

Did they know what they were talking about?

Yet another follow-up: is there a webpage or book on database design
philosophies, a sort of 'best practices' compendium. I have several (1000+
page) books on Access, but unfortunately, their hints and tips are scattered
throughout.\

Thanks for your service to this forum.

 

[Pat Hartman\(MVP\)]

A junction table is REQUIRED to implement a many-to-many relationship. I
don't know what the query suggestion was referring to but a query cannot
replace a persistent table to implement a relationship.

Here's a couple of links to get you started. There's a lot of stuff out
there but the quality varies widely.

http://www.geekgirls.com/databases_from_scratch_2.htm
http://www.geekgirls.com/databases_from_scratch_3.htm
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88