Programming with nested queries (1 Viewer)

[Twincam]

I need to bring some order to a query chaos! Issues:

Multiple programs and databases (.accdb) with many identical tables and identical queries.

Many queries that would be identical except for "CustomerID", "Customer", "Customer ID" field name type issues

Many queries that do the same things as other queries, except they total a different field, or do a left join instead of a right join, then have extra code to exclude the un-needed rows.

Nested queries that don't need to be nested.

A mix of running queries defined in the DBs, and queries built as text in the VBA code.

All these programs analyse the data and produce reports in the form of an excel spreadsheet, there's stuff being done in great wodges of access VBA code that the excel spreadsheets could do in one formula.

I need a plan....

My thoughts for the target approach:

One database with one set of tables.

A set of queries defined in the Access DB that do common cross-referencing, summing and grouping. Programs access those by query name.

Specific queries that are only required by one program would be inline in the individual program itself.

----------

Does that sound reasonable given that I may not complete the work and another contractor may take over? I can leave them with this nightmare, or half way through an organic change of structure where some programs are in the new format, some aren't and some are half way.

If you were dropped in it, which situation would you prefer?

 

[JHB]

..
If you were dropped in it, which situation would you prefer?

Did you ask yourself the same question?, (which one would you prefer?)?
It is difficult to take over a project, but one thing that helps is, like when you start a new project, a clear description of the goal and maybe how to get there!

 

[spikepl]

You have not mentioned the one most vital piece of information: is your plan to handle future DB's or is your plan aimed at revamping the existing ones?

In the future, absolutely do as you see fit.

If this is a revamping effort, then note that at the end, and after presumably putting much more work into it than envisaged at the outset, you have a system that does the same as it did before you began. But money/effort has been spent.

Make notes and observations about each DB, and tally, honestly, the effort(cost) that would be required to revamp it. Line it up against the future benefits (a structure revamp does not add functionality to an existing system). Then you have a decision tool. Note that one's offended sense of good programming practices does not count

 

[vbaInet]

My comments/advice:

1. Multiple dbs with same table structures would normally indicate that the original db had reached its limit and needed to be split. This covers your proposal about making it one db, don't.
2. As for identical queries that have different field aliases it was either a typo, your predecessors weren't bothered to re-use existing queries or felt that they could write better queries instead of using someone else's or they needed it in that particular format for a JOIN, report or form.
3. Many queries that do the same thing, except they total a different field - it makes sense to only include what you need in a query. You don't want to put all the totals in one query just because you can but you end up not even using half of the summed up fields.
4. Nested queries that don't need to be nested - if you can go from a subquery to a JOIN then yes performance will be greatly improved. On the other hand if you're thinking of going from a subquery to a domain aggregate function (i.e. DCount, DSum) then it's best you don't change it.
5. As a follow up to point 4, watch out for domain aggregate functions used in queries too. If you're considering changing those to subqueries you've got to be careful to locate where it's actually used. For example, crosstab queries don't play well with subqueries.
6. If you were paid to restructure all the dbs and it's within a reasonable time frame then yes it makes sense to re-do, delete or change some of the queries you feel are redundant. You'll need to know the dependencies and do a deep search to find where each query is being used. If you aren't, then I'll advise that you do just what you're paid for.
7. If you feel that you can do the same calculation in Excel more effeciently, then by all means do the calculation in Excel after outputting it and get rid of the formulas afterwards.

In your case it sounds like it's time to upscale to a database server, i.e. Oracle, SQL Server, MySQL etc if you want it in "one" db.

Finally, if the sole purpose of the application was to produce reports in Excel and Access is used to simply store and retrieve the data, then I would:
1. filter out the data in Access that's needed for the report
2. output it to Excel for further processing and calculations
3. tidy up the spreadsheet and remove all the formulas afterwards.

 

[vbaInet]

One last point, test, test and test again. This would be the biggest part of your venture. A very well structured plan should be in place for the testing phase.

 

[jdraw]

Twincam,

You mention another contractor may take over. Are you a contractor? What was the statement of work you were given?
Be cautious-- do what was requested. Clarify the statement of work if there is any confusion. There is nothing wrong with laying out your findings --number of duplicated databases/tables/queries etc. You could even create a data model overviewing the "underlying business". And use that model to highlight issues with the current environment.
You can identify what you see as the weakness of their data base/data management environment; you can describe some changes and highlight the benefits of same (in business terms); you can even give a guestimate of the effort/costs involved.
However, I would advise NOT to take on any change in data management without commitment from senior management. Any screwups because of your misunderstanding
of any process/system/database or failure to get senior management involved early or failure to get existing resources to buy in will all come back to haunt you(read that as scapegoat).

You're probably correct that things should be cleaned up and standardized, but you can not do this as a contracted programmer. You have to start with something supported by management with stated benefit.

If this is a viable(makes a profit) business, they will be reluctant to change. If they are losing customers; have slow delivery; improper source materials for manufacturing -- (read all that as some recognized business problem), then
that will be your theme.