New Company, access to old data

[BobbyDazzler]

I created a database for the company I work for 19 years ago. We are a small garage and there is a lot of historic data. The boss is retiring and I am taking on the company. I will continue using the same database but there needs to be a line drawn between the old company and the new company. Easy enough for me to start afresh but I'd like to be able to link to the old data to see vehicle details and historic invoices etc Anyone had experience of this that could give me some pointers? Thanks

[jojowhite]

you create a copy of your database.
name the copy as old_YourdatabaseName.mdb (accdb?)

now on the original db, delete all transaction records.
on this database, create a linked table from the old database.

[ArviLaanemets]

It depends on your database structure, how the data there is linked to old boss currently, an what will change when you'll be a new boss.

Maybe you need ta add something like Owners table, with ownership ID, owners name, and start and end dates of ownership. There you'll have a row for old boss, and a row for yourself. The end date of ownership for old boss, and the start date of your ownership must be same . And then you add field with old boss ownership ID in it to all tables where you need the ownership determined. And after the transition of ownership is set, all later entries in those tables must be registered with your ownership ID.

And surely you need an inventory of any resources (material, immaterial, monetary, etc.). In any table, where info movements of those resources are registered, you add a negative amounts equal to inventory amount at ownership change date in a way it links them to old boss, and same positive amount in a way it links them to you. Now when you need to get the amount of any such resource at certain date after ownership transition, you calculate them summarizing all of movements of this resource from ownership transition date until the date you are queryng the result for.

[CJ_London]

There are multiple ways - depends on your tables and whether you need to maintain a history of stock, client invoices etc

one way may be to create a table to contain a single record - the date of ownership change. Then when required, include in your queries

[Welshgasman]

I would probably copy the structure to a new DB.
Then import what might be needed, customer and vehicle details?

Then just open the old DB as and when if needed.

How often do you think you would need to refer to the old DB except for static data like customer/vehicle etc data?

[BobbyDazzler]

I would probably copy the structure to a new DB.
Then import what might be needed, customer and vehicle details?

Then just open the old DB as and when if needed.

How often do you think you would need to refer to the old DB except for static data like customer/vehicle etc data?

The main reference will be to see what work we had done previously, so info from tblInvoice and tblInvoiceDetail mainly but there may also be requirement for checking parts we've historically purchased for a particular vehicle - tblAcq and tblAcqdetail.

Perhaps just linking the old tables to the new front end and having a couple of forms which will show history if called from a specific form? We currently have a button on the invoice/worksheet form which shows previous jobs done on a vehicle. I could duplicate that for the old company. The normal button would show historical work carried out by my new company and the duplicate button do he same for the old company?

Thanks everyone

[ArviLaanemets]

How often do you think you would need to refer to the old DB except for static data like customer/vehicle etc data?

The possible situation:

The client left car for some repairs before old boss did retire;
Probably there was some work done on car before old boss did retire (but not finished);
Old boss did retire;
Repairs were finished.

How will you calculate costs, materials used for repairs, etc., in case of 2 different databases?

[Micron]

I read the OP more than once and I don't see why there has to be any sort of split at all. Vehicle details are not date/time sensitive, so there's that. Any repair history would be date/time sensitive (or should have been from the start) so your queries simply use an expression based on the date that you took over (e.g. >=#03/21/2025#). If a repair/service spans the transition date and you care about that, you get around that by using the start and end dates (which you also should have had from the beginning).

[BobbyDazzler]

@ ArviLaanemets - Won't happen. We're a fast fit garage, everything done same day. There is rarely a requirement to keep a vehicle so not a problem.

[BobbyDazzler]

I read the OP more than once and I don't see why there has to be any sort of split at all. Vehicle details are not date/time sensitive, so there's that. Any repair history would be date/time sensitive (or should have been from the start) so your queries simply use an expression based on the date that you took over (e.g. >=#03/21/2025#). If a repair/service spans the transition date and you care about that, you get around that by using the start and end dates (which you also should have had from the beginning).

Like this! But for accounting purposes a clean break is preferred. Appreciate your input.

[NoellaG]

a clear break can be: add the field company to the main table and in day-to-day operations you only load data from the current company. Queries faster than date/time criteria and is legally correct for accounting purposes.

[Welshgasman]

The main reference will be to see what work we had done previously, so info from tblInvoice and tblInvoiceDetail mainly but there may also be requirement for checking parts we've historically purchased for a particular vehicle - tblAcq and tblAcqdetail.

Perhaps just linking the old tables to the new front end and having a couple of forms which will show history if called from a specific form? We currently have a button on the invoice/worksheet form which shows previous jobs done on a vehicle. I could duplicate that for the old company. The normal button would show historical work carried out by my new company and the duplicate button do he same for the old company?

Thanks everyone

I would go with what CJ_London suggests then.
Create a new field and populate it with something to identify the old boss and yourself.
Populate it all if you can identify the data with his name/id, and then have yours as default for new jobs etc. Not sure you wuld need it for customers etc, unless you wanted to identify new customers since you took over?

Or just remember tha date you took control and compare the data to that?

[NoellaG]

Not sure you wuld need it for customers etc, unless you wanted to identify new customers since you took over?

New customers are easily identified as users that don't have any invoices/operations records before the new company took over.

[BobbyDazzler]

Thank you all for your time and effort. All sorted.

[Welshgasman]

Thank you all for your time and effort. All sorted.

Well do tell us what you went for?
It might help others in the future.