Why queries & not tables?

[Antinora]

Greetings:

I know that best practice is to use a query as the data source for reports.

My boss asked "why can't we just use the tables?"

I didn't have a clear answer besides "convention".

Anyone know the answer to this question?

Thanks & enjoy the day,

John

 

[Ranman256]

Because people always want change.
It's better to change a query,than redesign a table.
Queries can also provide functions.

 

[JHB]

You can use table if you want, it doesn't have to be a query.
Mostly it depends of the type a report you want.
If you want to pick some data from different tables you use a query.
If you want to write out the whole table, then use a table.

 

[plog]

I know that best practice is to use a query as the data source for reports.

Citation neeeded. Where did you hear this best practice?

 

[Tieval]

A table is a query, it's SQL is SELECT*

 

[The_Doc_Man]

For the case of a split FE/BE, I have seen that if you open a Form or Report directly to a BE table, you sometimes get a question about "where is table xxx?" - usually because of some obscure error in setup. HOWEVER, if you declare queries for all tables, the queries somehow store the table location in a way to prevent that question.

It happened to me some time ago and I'm damned if I recall the exact configuration mistake I made that caused it, but I recall clearly that going through a query stopped it from happening.

Here is another reason, though. Suppose you want TWO reports from the same table, with GROUP BY and ORDER BY clauses that interchange between two fields. I.e. one report groups on A and orders on B; the other groups on B and orders on A.

If you do two queries to implement that information, the reports, each with a ready-made query to do the work, become nearly trivial AND you can easily open the queries in datasheet view for visual debugging or confirmation of what the report generator sees.

If you do two reports directly to that table, the report wizard (or YOU) will have to build those two queries anyway. If you do the latter (two direct reports), go ahead and create the reports via your friendly local Access report wizard. But then, open up those reports in design mode. You'll see that the .RecordSource property is probably a name beginning with the tilde (~) character, which means that the wizard built a "hidden" query that won't appear in the "Queries" segment of your navigation panel. You would have to open the mSysObjects table to see that name - but it would be there (and other than a weird prefix, would strongly resemble the name of the report object.)

Now, here's the reason you use explicit queries. Make ONE CHANGE - Just ONE - to that underlying table and when you do, the direct reports die because those hidden queries become invalidated. But if you have explicit queries that name the table, you can use the Object Dependencies feature to know the names of the queries that need to be updated. Everything is open and above board for any subsequent maintenance.

Therefore, inform your boss that the answer to his question is (a) to streamline how the table is opened and (b) to consider future maintenance costs by having the objects to be maintained stay visible and easily found, because (c) the reports will have their own private .RecordSource queries anyway. So you WERE using queries and didn't realize it.

 

[gemma-the-husky]

the query can be a sorted view of the raw data in a table

if you just use a table, you just get the default sort order.

besides which the query need not return every column from the table.