Multiple large queries vs. One big/several small queries

[J Sedoff]

I have several queries (MB59, MB59 Skimming, MB59 RD, and MB59 Obsoletes)
that are retrieving the same data (MB59 data), but are applying different
filters so that altogether they retrieve all the data (qryMB59 has all the
leftover data not included in the others).

I have a report (MB59 report) that brings all these queries together in an
organized manner. It takes a long time for the report to compile, so I was
wondering: Is it more computer-efficient/faster to have them run through a
common query (say: MB59-all) to mass collect the data and apply some basic
filters, then run each of those queries separately to apply their specific
filters; or is it better to have each of them run all the data and apply the
needed filters.

In other words, is it better to have one large general query that does most
of the work and is then referenced several times by several specialized
queries, or is it better to have several semi-related specialized queries
that each do all the work?

Thanks for the help,
Jim

 

[Jeff Boyce]

Jim

There are so many answers to your question...

Where's your data? In an Access .mdb file? In a separate (i.e., "split"
Access back-end)? In a SQL-Server back-end?

How do you get at the data? Is it on your PC? On a Local Area Network?
What speed? Full-duplex? On a Wide Area Network?

How is your data structured? Is it well-normalized or "flat", like a
spreadsheet? Do you have indexes set on fields used for selection criteria
in the queries? For sort and/or grouping fields? For fields used to join
tables? How many joins? How many tables?

The best approach I can offer is to test it in your situation to see which
approach runs faster. And if you are experiencing performance issues,
consider all the above before you look to finding more efficient queries...

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J Sedoff]

2 of the tables are internal to the .mdb file, but most of the data (6
tables) are updated in Excel (.xls) files. I've been wondering if we should
split our database (how does one do that, and when is it worthwhile?), we are
populating the Excel files with new data, and the rest of the database is
solely used for running the Reports, as well as a few queries tooled for
Excel to use in a summary file where we tap into the database using Excel's
"Get External Data" function.

The files are in the same folder on the company's network (I presume LAN,
but I don't know for sure).

I would love for it to be normalized, but it is flat (like a spreadsheet).
Since most of the tables are linked, I can't index them (unless you know of a
method?).

I have a couple of joins per each query, and several relationships set up in
the Relationships window.

I don't know if this setup pigeon-holes me into a particular method. I know
that the database is not the best, I didn't make it, but rather have been
learning how (I haven't used Access since a Intro to Office Apps class in
middle school) to solve some short-term problems that the users have had in
the past few years since its creation by what some called an Access-guru who
is no longer with the company.

(Jeff - Thanks for the preliminary help!)

Any help or recommendations are greatly appreciated! Thanks, Jim

 

[Jeff Boyce]

Jim

Even though you are linking to data in Excel spreadsheets, you are not
limited to that. A common approach in using Access to get at "flat" data
located elsewhere is to treat that data as "raw, temporary" data, probably
not well-normalized. Then create a well-normalized table structure that
will hold your "permanent" data in a table structure that lets you use
Access' relationally-oriented features and functions. Finally, create
queries that "parse" the raw data into the permanent tables.

From there, you create/use queries, forms, reports against your permanent
tables.

Yes, you end up storing data in Access, but you store it in a format that
Access (and you) can more easily use.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J Sedoff]

(hate to be a bother).. sounds great, but how do I do that?

Do I still keep the linked tables, but make new (non-linked Access) tables
that use the "Lookup" data type?? How do I ensure that it is the correct
data type (I have some columns in the linked tables that Access determined to
be "Text" although they are really a "Number" type, so I can't change the
data type)?

Thanks for the help, Jim

 

[dustinbrearton via AccessMonster.com]

(hate to be a bother).. sounds great, but how do I do that?

Do I still keep the linked tables, but make new (non-linked Access) tables
that use the "Lookup" data type?? How do I ensure that it is the correct
data type (I have some columns in the linked tables that Access determined to
be "Text" although they are really a "Number" type, so I can't change the
data type)?

Thanks for the help, Jim

You can use Add, Update and Delete querries for this. Yes you would still
keep your linked tables and make one new non-linked table, a place to compile
all of the info from your linked tables and use as a base for your report.
As long as you don't care to retain the old information you would likely only
need the add querries. You can change the data type through in the non-
linked table you create. I would only change the numbers to numbers if you
plan to make calculations with them. If they are just serial numbers or some
kind of reference then leaving them as text will likely be better in the long
run.

 

[Jeff Boyce]

Jim

The idea is to do away with the lookup data type in tables all together.

If you are only working with linked tables, you'll have to go to the source
of those linked tables to make the changes.

Regards

Jeff Boyce
Microsoft Office/Access MVP