Access Database

[Gill]

Hi,

Up until now we've been using Excel to manage all our administration side of sales history, customer orders etc but this has involved a lot of extra data entry rather than using the exported csv files.

We would now like to use Access to manage this and I thought setting this up would be straightforward.

This is the way I thought it would work, please could someone advise me as to where I am going wrong?

I have taken a copy of the actiniccatalog.mdb and opened that in Access. I thought then from that point I could just regularly import the exported .csv files from Actinic to update the Person, Order, Ordelines and payment records. However when I do this, the set up of the fields in those records in Acccess are different from the set up of the fields in the exported csv files. There are some fields that are in the access record that are not in the csv file and vice versa. Also the properties of some of the fields are different as well. I could spend a lot of time sorting out all these fields but maybe I don't have to? In the access database side a lot of fields have an extra letter in front of the field name (usually an 's' 'n' or 'd' which are not in the csv file.

I've searched through the postings re the administration side of actinic and haven't found anything - how do other people deal with this? Has anyone set up an access database that deals with all these issues?

It seems to be a very long process to check all the field properties in Access and then compare them with the field properties in the imported csv files and change them accordingly. Also I would have to then check all the linked records to ensure the integrity of the links still hold.

Also, when I import the person file, how do I link it to the orders file so that only the invoice id is shown rather than the delivery ID? I'd like the record to show the full details of the invoice including the delivery address.

Please could someone help? Thank you!

Gill

[Mike Hughes]

I can't help on the access side, but did you realise that excel can access the actinic database directly instead of using the exported csv files. This is the way I do it.

Mike

[Gill]

Thanks Mike, if I can't get Access to work how I want it, then I'll be very interested in how you do it in Excel. I think you've said before that you use Macros to automate a lot of it?

I think this next question will relate to either Access or Excel. When a customer visits and purchases twice from the store, how do you get either Access or Excel to see that it's the same person? I don't seem to be able to get it to know that it's the same person and it puts the customer twice.

Also, in the access database, the currency fields don't have the decimal point in the right place so an order for £55.30 is £5530. Do I have to run a macro to change all these or is there somewhere in the settings that can do this?

I'm really surprised that there aren't Actinic Access Databases already out there that are set up with tables, forms, queries and reports that we can just 'adopt'.

Gill

[Mike Hughes]

Hi Gill,

Getting the data is easy. I really only use the macros to process it.

To get data from the actinic database all you need to do in excel is:

Data> Get external data > New database query

This open up a dialog box called 'choose data source' and all you need to do then is select 'MS Access database' and browse to the right file.

You then just browse through the database, selecting the tables and data sets that you want to import and excel does the rest.

To repeat the process you can save the query (and then run it later using: Data > get external data > run saved query) or else just record the process using 'Tools > macro > record new macro' which will automatically create the macro for you.

I can't remember if the external database stuff is loaded by default in excel or you have to install it from the CD. The easiest way is just to see if the 'get external data' option exists.

Mike

PS. And of course, once the data's in excel, correcting things like pence to pounds and pence is dead easy.

[Gill]

Is there a guide anywhere that says which tables are linked to other tables and by which fields?

[Jan]

I wouldn't recommend message about with the Actinic database directly unless you are very sure of what you are doing.

The easiest way to import the Actinic export files is to create the files, create a link to the csv files in your access database and then write queries to append them to the end of your tables.

The links are

order - order lines
Order sequence number - nOrderSequenceNumber

order - person
InvoiceContactId - ContactID for the invoice row
DeliveryContactId - ContactID for the delivery row

order - payments
Order Sequence Number - OrderNumber

Regards,

[Gill]

Thanks Jan,

I suppose what I was really asking...

I can create the Access file as you suggested. I also tho, want to be able to view orders and orderlines by customer, (and the other way around), and lots of other useful forms/queries where I can input data (for example for adding orders that are taken over the telephone rather than via the site). I also want to import my product cost table so that I can write P/L reports.

There must have been loads of those written for use with Actinic, and I'm surprised that I haven't seen any 'advertised' here to save people like myself trying, unsuccessfully, to reinvent the wheel.

I'm very interested in how other people using actinic store and query the information that Actinic collects.

I'm a total novice with Access (for example, I've been trying to get what I want by using the Forms, hadn't even looked at queries as I assumed that was to query the information that you had imported).

I think once I had the basic 'template' of the access database set up I could then use that to see how it all worked, and then add my own queries, forms and reports. Well, I hope so!

I've also had Alpha5 on trial for a few days - have to say it's made Access easier to understand!

Thanks

Gill

[Jan]

The thing to do it to create queries that contain the data that you want to see and then you can create a form based on the query. This might not be the best way to do things, but I find this to be the easiest way. By doing this you are splitting your problem between getting the right kind of data and then displaying it in an easy to use way.

I think that one of the reasons that people do not sell their own external systems is because firstly they need to make them saleable which is actually quite hard work - you need manuals, online help, security systems and then once sold you have to support the systems which again is hard work. Also (in my opinion this is the main reason) everyone needs to do things slightly differently so what is perfect for one is of no use to another. People that need standard functionality can get that by exporting the data from Actinic into other off the shelf systems such as Sage or ACT.

You might find that you get more help if you list out what you are trying to do, there will probably be people around that have done all or part of it.

Regards,

[Gill]

Thanks Jan,

Ok, this is what I'm trying to do...


List by person, orders and order lines - with a separate section on the form that shows the delivery ID details. If a customer has ordered more than once from us (which happens a lot), I want to show all orders for htat person. For some reason, it creates at the moment, a new person ID every time rather than recognising the person already exists.

I think this would get me going enough to be able to figure out how to do the rest...(VAT records, stock levels etc).

Thanks if any one can help!

Gill

[Jan]

Have you got catalog or business?

Regards,

[Gill]

Hi Jan,

Catalog,

Gill

[Jan]

You could use the transaction by referrer report in Actinic, this uses the user defined general field to group order details together.

You would need to copy the contents of something like the email address field or the postcode to this field for the report to work, you could do this with a query in access.

Note that the report is grouped by product sold for each referrer (or customer in your case) so adjustments will not be shown.

Actinic doesn't group orders by customer unless you have business and the customer logs on before purchasing. So you will get two entries in the persons table for every order.

I like the idea of showing orders by customer and will probably write a report along the lines of this either for my free library or as a new product - I might add them to by sales analysis report set actually. If anyone else needs this sort of thing email me with your requirements and I will consider including them.

For VAT you can use the tax summary report in Actinic

For Stock levels you can use the stock level reports in Actinic.

Regards,

[Gill]

Hi Jan,

some of those reports in Actinic appear to be just printed reports. I know I can print to a file, but the stock level report would be better (I believe) if I export the catalog to access, and then have some query that when the catalog is imported the next time, compares the entries in the stock level field and flags the products which have changed to below the set level since the last time the catalog was imported - or am I being to ambitious?

Is it possible to manually tell access which customers are the same?

thanks

Gill

[Jan]

Hi Gill,

All of the solutions I suggested are reporting solutions. Catalog has no concept of 'customer' so any linking will have to be done manually.

It sounds as if you are embarking on quite a project, I don't think you are being too ambitious but I do think that it will take time to do.

Regards,

[fabrique]

why is the database undocumented and closed ?

why is the database undocumented and closed ?

why should it be such a major undertaking to take
the actinic database and use it as a conventional database.

our current ecommerce system has a database that
can be used with all manner of queries and reports,
because it's documented and it's easy to discern the structure.

exporting and importing to it is very simple,
and we can create custom reports and forms.

not only does this not appear to be possible with actinic,
but it doesn't even seem to be a priority.

this is particularly troubling because we ACQUIRED
actinic, and discovered that it's csv product exports can not
be easily adapted to any schema we can recognize,
and talking directly to the access database is going
to be a serious customization and programming effort.

i think that actinic should either create a db schema that's
easy to query and use in conjunction with other tools,
or the company should clearly state that it's not within
the capability of the product.

we're out $1400 for a product which has impressive capabilities
in several areas, but seems to be ultimately unusable
as a tool in conjunction with other tools, due to the lack
of communication between the actinic database and anything else.

please, please correct me. i want to be corrected.
i'd love for this product to be usable with our CRM,
communications, financials, and other tools.

but once i started working with the actinic database...
or the "csv exports"... i realized that it's near impossible.

while the template interface is interesting and has a good
deal of power, the rest of the system seems poorly thought out.
there are no means of sorting or filtering products without
an external plug in ! and even the plugin doesn't allow for
even the most basic filtering. the search tool is also a joke.

making thumbnailed products is also an awful hack.
putting products in multiple categories is also an awful hack.

please help me out here.
i recommended the solution because i saw it's promise.

now that we own it, i can't see it as being part of
a high volume, 1,000 product ecommerce enterprise.

having an undocumented database schema is a huge problem.

our current cart has nowhere near the interface features of
actinic, but it has a database that can be easily used with other
tools, and that's why we are still running it today.

i was supposed to port to actinic, and i can't make that
recommendation now... we have to backtrack. maybe
even try and get our money back. this is costing more
time and money than the purchase price, a lot more.


help ???

i do so badly want to be wrong about all this.