Access 2000: Version comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there -
I'll do my best to explain my dilema.

I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
reports. I've set up input forms for staff to enter data easily. There are
8 staff members.

My boss wants to ensure that data entered into this database is correct
right off the bat. She's asking for a program or code to do the following:

- Auto-generate a form immediately after someone has updated the database
with new data
- The report should compare the latest backup version of the database with
the current "live" version
- The report should take the fields that were updated in the "live" db, add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate" the latest backup copy as the live version

I need to know if this sort of thing is possible. If so, how long would
something like this take to do? Should I hire a contractor to custom-build
this? Is there an existing program out there that can do this?

Any advice or suggestions would be helpful.
Thanks a million!
Tara
 
Tara,


I'm still not clear about your problem.
I'm using Access 2000. In this database, there is one table with about 150
columns of information, and 206 rows. There are numerous queries and
Click to expand...

150 columns on a table? What are they? It's hard to imagine a table needing
to be that wide.
You may need to normalize the table. Yes, this may mean *back to the drawing
board*

- Auto-generate a form immediately after someone has updated the database
with new data
- The report should compare the latest backup version of the database with
the current "live" version
- The report should take the fields that were updated in the "live" db, add
them in the report, and do the same from the backup so she can compare the
old vs. new data
- If the data is incorrect, she wants this program to automatically
"reactivate" the latest backup copy as the live version
Click to expand...


I'm not sure what you're doing here. All these requests may be the result of
the poor design of the database in the first place (i.e. lack of
normalization mentioned above). You know, a big function of database system
is to prevent *incorrect* data to be entered in the first place. In other
words, a well designed system would make the above requests moot. You would
not need to compare your *live* version against your *backup* version
because your *live* version will always be correct.

I need to know if this sort of thing is possible. If so, how long would
Click to expand...

What thing? You havent described what the system does, and what it's
supposed to do.
this? Is there an existing program out there that can do this?
Click to expand...

It's hard to tell if there is any existing program out there that can do
"this". What is "this"?
Again, please describe what the Access database is supposed to do? Right
now, I cant tell if you're a hair dresser or a medical doctor.


HTH,
Immanuel Sibero
 
I'm using Access 2000. In this database, there is one table with about
150 columns of information,
Click to expand...

That's not a table, it's a spreadsheet.
My boss wants to ensure that data entered into this database is
correct right off the bat.
Click to expand...

Rather than faffing about with audit trails and backup reconciliation the
like, I'd vote for a serious redesign of this monolith into a properly
normalised database with an appropriate number of tables.

Failing that, what about using Excel with its excellent Data Validation
methods?

All the best


Tim F
 
Immanuel -
I'm still not clear about your problem.
Click to expand...

I'll try to explain a bit better. But first, I'll answer your questions.
150 columns on a table? What are they? It's hard to imagine a table needing
to be that wide.
You may need to normalize the table. Yes, this may mean *back to the drawing
board*
Click to expand...

Sadly, this is not possible. I work in the government, and this database is
used to track all funding, staff and insurance (among other things) of each
and every agency in our juristiction. That's over 206 rows right off the bat.
With all the data they want tracked, our column count is at about 150 at this
point.
The database has been whittled down to one table, because multiple tables
were causing a lot of 'slippage' and 'data jumping' problems. (someone would
add a new agency to one table, but not the other...blah blah blah)
I know to an Access pro, this may seem like a crazy idea, but the one table
system works perfectly. Why? Because all the data relates to the one unique
Agency Name. It makes generating reports, inputting into forms, and adding
new fields a snap.
Long story short - it works, they're not willing to change the design.
I hope that made sense. Sorry if I wasn't clear.
I'm not sure what you're doing here. All these requests may be the result of
the poor design of the database in the first place (i.e. lack of
normalization mentioned above). You know, a big function of database system
is to prevent *incorrect* data to be entered in the first place. In other
words, a well designed system would make the above requests moot. You would
not need to compare your *live* version against your *backup* version
because your *live* version will always be correct.
Click to expand...

I think you're a bit off the mark here, because I guess I didn't explain
very well.
My boss doesn't trust that the people doing the data entry to do it
properly, or in the correct fields. This is the reason for the comparison. I
know it's silly.
What thing? You havent described what the system does, and what it's
supposed to do.
Click to expand...

I thought I did - to generate a report using the newly updated fields of the
"live" DB to the backup DB, to make sure that the data entered is correct.
It's hard to tell if there is any existing program out there that can do
"this". What is "this"?
Again, please describe what the Access database is supposed to do? Right
now, I cant tell if you're a hair dresser or a medical doctor.


HTH,
Immanuel Sibero
Click to expand...

Again...hope I was more clear this time.
Thanks for replying!
 
Hi Tim -
Thanks for replying.
As I said to Immanuel, sizing down this monster DB is not possible since it
tracks all government agency data. (206 agencies, so far)
The database has been whittled down to one table, because multiple tables
were causing a lot of 'slippage' and 'data jumping' problems. (someone would
add a new agency to one table, but not the other...blah blah blah)
I know to an Access pro, this may seem like a crazy idea, but the one table
system works perfectly because all the data relates to the one unique Agency
Name. It makes generating reports, inputting into forms, and adding new
fields a snap.
Long story short - it works, they're not willing to change the design.

I'd like to change her mind about this content audit. I think people will be
careful enough when entering using an input form. But, she doesn't trust the
software or the people entering the data. Sure makes my life difficult!

Anyway - if I could use Excel, I certainly would. It'd be 10x easier, I'm
sure.
I appreciate you writing back. :)
Tara
 
Will do! Thanks!!

RD said:
Hi Tara,

I've read the other responses. I work for a gov't entity as well and know what
you're going through as far as working with a db designed by somebody else.
Shoot me an email at rudraighATgmailDOTcom and I'll see if I can lend a hand.

Regards,
RD
Click to expand...
 
It makes generating reports, inputting into forms, and adding new
fields a snap.
Long story short - it works, they're not willing to change the design.
Click to expand...

You mean you actually waste new resources and time to build new reports
every time a field is added? Who pays for this wastage? Can you imagine if
your accounting system required developers to modify reports and screens
every time a new vendor, or account needs to be created?

The fact of the matter is that when you add a new column, then all of the
efforts, and designs for EXISTING reports have to be modified. This is a
formula for a never ending situation where the application design needs to
be constantly modified. Contrast this to a accounting system that can allow
all kinds of new things to be added over time..but NO NEW software designs
must take place.

With a proper normalized system, the addition of new fields will be
EXTREMELY RARE. If you have to constantly add fields, then how can you build
a form to edit and maintain this data? (and, to properly verify input..and
ensure mistakes are NOT made during data entry, then you need a form).

If you have to add a new field to the application, then the forms, code you
write, reports you build etc, and kinds of parts of the application must be
modified, and changed. Now, if you only got one report..this might be
workable, but over time, as you add more features, and more reports..then
the whole thing will become a un-mitigated disaster to maintain, and run.
You simply will be wasting time and money that could help the poor, or do
other important things....
 
Back
Top