Table vs Query question

[ck1999]

I was asked to look over a database someone else has made. Almost all the database tables have associated queries that are used by forms.

For example:
Tblemployee and qryemployee all contain the same fields with no filters. Yet in a form to add or delete employees the person used qryemployee instead or tblemployee.

They did this for about 5 different tables. Is there a reason not to link to the table instead of using the query.

Currently the database is not split, but is to be used by multiple people. Using Access 2003.

Thanks for your help.

ck1999

 

[fneily]

If they truly are using all the tables fields in the query without any filtering, then No. Maybe someone taught them to do that. Most people don't learn to use Access correctly. Or they might be anticipating using parameter queries. Who knows.
Question: what happens when a field is added or deleted to a table? Then you have one more object to change which is the query. Of course, you shouldn't be adding or deleting fields if you planned correctly.
I'm also assuming they are not checking the changes first before any editing or additions.
Concept: queries are just table subsets. That's why tables and queries are both called recordsets.

 

[JoeAtWork]

There is an advantage in using queries over tables, in that it adds a layer of abstraction, which makes future updates easier to implement.

Let's say you have a table called Products, and a query called qryProducts that has all the fields of the table. You bind a form and a report to this query.

Now let's say in the future you decide the form and report should filter out all discontinued products. You would need to make the change in only one place, qryProducts.

This example may seem trivial, but imagine scenarios where dozens of different forms, reports, code, and other queries are dependent on this "base" query. The query is in fact applying a layer of "business logic", while the table is the pure data. For this reason, some programmers advocate always binding forms and reports to queries rather than tables.

 

[ck1999]

Thank you both for your replies. Upon looking at all the tables and queries I beleive fneily is correct in that the person who developed the database was either taught this or does not know any better. Some of the tables and related queries only had 2 fields. Like autonumber and employee name. That was all in the origional table.

Thanks again both of you for you insight.

ck1999

 

[JoeAtWork]

Some of the tables and related queries only had 2 fields. Like autonumber and employee name.

That by itself does not indicate a bad design. Having two fields, or maybe even one, is perfectly legitimate if that is all that is needed.

Good design is judged by how well the designer followed the rules of database normalization.

The fact that the original designer was so consistent in creating queries shows he was following some sort of methodology. Usually that is better than those who make additions willy nilly.

 

[missinglinq]

And the fact of the matter is, basing forms on queries, even if the query only contains a single table, is considered best practise by most experienced developers today. Having a query as a recordset allows so many things in the way of data manipulation to be easily accomplished.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!