Can I merge orders?

[Eileen]

Hi there,
My client's hard drive bite the dust, taking with it his actinic database and snapshots, etc.

We installed Actinic v8 onto a new PC, using an old snapshot which I had from 6 months ago. The corrupt hard drive was sent to a data recovery firm who were able to recover our actinic database.

So now... is it possible to MERGE the two sets of orders together, to create a complete set? I notice that the IMPORT ORDERS function replaces the existing orders with the imported ones, which is not what we want.

We want the orders from the old and new databases merged by date.

any help will be appreciated.

In the meantime, I have exported the old persons table to a spreadsheet and filtered out the duplicates, so they can cut and paste name and address info into new orders, but they have lost a lot of customer info and this is quite tedious

[leehack]

There's no standard actinic way of doing this, it may be possible to merge via the databases, but it sure sounds like trouble trying to me.

[chris ashdown]

Probably missing the point and talking rubbish, BUT

If you have recovered the MDB file then what is stopping you from just copying the recovered actinic.mdb file into actinic site folder and you should have all data

[Eileen]

I am sorry Chris,

but yes, you are missing the obvious:

the new database holds the current orders.
the recovered database has the older orders.

we want to merge the two sets of orders.

The reason we want to merge them is that we get a lot of off-line orders from repeat customers - it is very tedious to enter all the contact information.

I have created a spreadsheet from the Persons table in the recovered database and at least they can view that and cut and paste, but still it would be much simpler if we could merge the old data back into the current database.

cheers,
Eileen

[guccij]

I'm wondering if you could use WinMerge to do this for you? http://winmerge.org

[Duncan Rounding]

The database is complex when it comes to orders, order details and customer information and hence I don't believe it's feasible to merge orders manually.

Best you can do is to export the old ones to csv and refer to that file should those customers reorder.

[Eileen]

which is exactly what we are doing, Duncan
thanks for the confirmation

cheers

[Eileen]

Hi Jules,
WinMerge is for text based files, not for Access databases.
cheers

[gabrielcrowe]

i have had this problem a few times. it'll take more than a simple sql query to fix this one.

the order table and the orderline table are connected by the key 'order sequence number'. this key increments, as orders are added. the problem is that when you merge the tables, this number collides (has the same values in more then 1 entry) and its a unique key.

coupled with this, the ID's from the persons table can also collide.

so, the only way to do it would be to extract each order, line and persons individually, and then re-insert them with fresh keys.

This is all rather conceptual by the way, and i feel that Catalog.exe may panic, and ahve some sort of order related wobbler.

I have given this a quick shot in [insert script lnaguage of the day here], and it's too complicated to do for free

alternative:
Buy a copy of Actinet. you can point actinet to any locally stored MDB and use it as an order archive.

[Eileen]

Hi Gabriel,
I feel for you, having had to do something similar in the past.
Myself, I do my actinic work for a friend for free outside my day job. I simply don't have the time right now to code a solution for a one-off thingy for him.
I think he will have to live for a month or so with the spreadsheet to hand and do copy and paste as needed.

cheers

[guccij]

Hi Jules,
WinMerge is for text based files, not for Access databases.
cheers

Thanks for clearing that one up. I was thinking of the spreadsheets you mentioned.

[Jan]

What I usually suggest that people if the number of orders to merge is small is to enter them again manually, making sure that emails are not sent to the customer and then to edit the database and set the order number to the one from the actual order. The order number isn't used as a key to any products so it's safe to do this. Obviously as always, any edits you make to the Actinic database are completely at your own risk.

Regards,

[carbontwelve]

Once again actinic makes a simple task a marathon to complete. If you know someone who is adept at intermediate database programming then they should be able to mock up a quick app which takes the two databases and amends one to the other.

I am pretty sure this is something that can be come within microsoft access too, I remember in one of my database programming lessons having to write a visual basic app which took two access databases and apended one to another while highlighting any duplicate records so the user could decide if to add them or not.

If I get time and there is still a demand for such a utility I shall find my old copy of visual basic studio and compile up a version. Im all into open source so it would be free if I did so. Just need to know if there is a demand for such a thing.

[Duncan Rounding]

I hear your argument and I understand the frustration but in this case unfortunately it's not simply a case of appending data to tables. The relationships between the order, order details, persons and products not to mention pricing and payments is complex - hence Jan's comments above.

[carbontwelve]

I hear your argument and I understand the frustration but in this case unfortunately it's not simply a case of appending data to tables. The relationships between the order, order details, persons and products not to mention pricing and payments is complex - hence Jan's comments above.

I realise this, however an experienced programmer would be able to build an app to do the task.

While the structure of linked keys, cross referenced tables and all the rest of the fluff that makes actinic's database tick is complex, it is not impossible to understand and as the guy has the same database duplicated with the only difference being one has a few more new orders than the other, I cant see it being too difficult to write an app to append the new orders to the old database considering the linked ID's and cross table fluff would be the same.