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?
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?