Need a little advice

[stm8ter]

I need a little advice or review. I have a Data Base in Access 2000. I have been working on this for several years. The Data Base works pretty well for the reasons it was originally intended, however it has grown in directions that were not even thought of and I may need to go back and revise some things. The original objective was limited to Project Entry and Estimate Tracking. Once an estimate became a contract it was basically a dead issue in access. Now the Project keeps going through access from the Estimate Phase, Contract, Purchase Order, Labor Tracking, Job Costing, Project Close, and Warranty, finally becoming and archive file. There is quite a bit of data stored, but I may have to make changes to move in a new direction.
The Main Form where everything is based is the Projects Entry Form. This form has unfortunately been placed into use for some things it was not originally intended it is large and complicated for data entry. There is a Sub Form for Bid Entries; in the early days we just entered a bid for the same project to (2) different contractors twice. This seems to have grown into a large amount of duplicated information. So here it comes, does anyone see an easy fix here? I think I need to create a sub form in the Bid Entries Form for the contractor entries. Then I will have to go back and update all of the duplicated entries, while always being concerned that this change will have a negative affect somewhere else. This will keep the estimate amount from being duplicated down the line and also allow me to pull reports on who was bid what.

 

[Parker]

I think I need to create a sub form in the Bid Entries Form for the contractor entries.

I think you're right. This would be a good route to go down.

You could rationalise a complicated form by putting some stuff on "tab controlls" this will tidy it up a bit.

But, as you imply, the next bit is, potentially dangerous. Do you have any way of creating an Audit trail on exsisting information?

It's fairly easy to edit duplicate information but if you delete a duplicate entry then how will you know which one is the right one?

Delete the wrong one and you could be in deep do do.

I think this problem is a common one with companies. I have, recently, won a case where a company was trying to claim two accounts from me. I'd paid one but for some reason they had me duplicated and kept billing me twice for everything. It took a court case to sort it out because they had so much belief in thier systems.

This is the process I think I would do.

First STOP the possibility of any more duplications.

Then find out just how bad is this situation. i.e. how many dupplicated records and how many are still current and active?

Then go through a process of identifying which of the duplications are relevant.

Safest way from here is if you can combine duplicated records into one record with the most relevant information as default aqnd the other listed behind.

If you can do this then as a contract dies you can, eventually, delete unnecessary information and rationalise the record.

Just a thought.

 

[stm8ter]

Advice

Thank You
I am trying to figure a way to stop the duplicates, however the key here is how many places are duplicates being produced. I found this problem by creating a report to track project estimates and awards. I was getting to much information. I posted a thread in reports (To much Information) but did not get any suggestions. I knew the report was corrupted, but thought there may be an easy way out. I know there is a problem with the duplicate contractors entry so that is were I need to go first.

I am not sure how to do the Audit Trail, but it sure sounds like a good idea, I will try to figure it out.

Thanks again for your help.

 

[Parker]

I seem to remember seeing some posts here on auditing but don't know if they would apply to this or if any examples could be applied retrospectively. Must be worth a look though.

As for stopping duplicates in the future how about this:

Give each contract a unique account.

Include a routine in your "Add new whatever" to search for an existence using criteria that includes enough about the client to pick up on duplicates. (you could use telephone numbers or post code as well as names)

If an account already exists then interrupt the process and give the data entry opp. the choice of editing the entry or creating a new entry (you could easily double safeguard this by bringing up the possible offending record)

I missed the post you quoted but I'll go look now

 

[The_Doc_Man]

Regarding auditing, just remember this: Access doesn't audit. It only allows you to build the auditing feature into your forms (and reports). The catch is that you have to do the work in VBA, so you cannot audit changes made where VBA doesn't run. That pretty much limits you to forms and reports, but you don't make changes on reports.

The way I approached auditing was to put some code in the Form_BeforeUpdate event. I opened an auditing recordset, just in case it was needed. Go through the form, look at the .Value and .Oldvalue fields, and compare for changes. Then write an entry for each change between the .Value and the .Oldvalue fields.

Note that the way Access works, it is possible that some properties are not expressed at all if they would have been null. So you cannot "trust" that a particular property is always there. Just be careful to enable error trapping so you can catch the case of asking for non-existing properties.