Editing product database directly

[stualk]

Hello,

I've done a site which contains over 3,000 products but i've put all the prices in ex VAT. We've now decided it would have been better to put the prices in inclusive of VAT. What I now intend to do is to export the database form the main access database into excel, edit the price field so that it adds 17.5% to the prices and the export the excel file back to access.

Before I do this does anyone know of anything I should be aware of or would anyone recommend not doing this at all? I've been fiddling around it I think this will work, but from past experience does anyone know of a better way of doing this maybe?

[gabrielcrowe]

you dont need to export anything.

the col in the database can be copied into an excell sheet, modified and then pasted back in.

1 - in the 'product' table in access, you'll find a col called 'price' clicking on its header will highlight the entire row, Ctrl+C (copy it).

2 - Open excell, click 'A' col header to highlight the entire col, and Ctrl+V (paste). It'll complain about being the wrong size, ignore then it'll paste the entire col.

3 - Do your vat calc, in the col immidiately to the right of the top NUMBER entry, _not_ the header word price:

=A1+(A1/100*17.5)

4 - Using the little gripper, the square one, to the bottom right of the item, drag your formula down, so that it replicates to all the required cells.

5 - Copy this newly calculated cells, from the first number to the last, making sure you dont leave ANY spaces or miss any out, copy them.

6 - Click 'prices' col header again, paste them back into your product table.

7 - CHECK it's what you wanted.

I cant stress how important it is at this point to
BACKUP YOUR DATABASE IN CASE YOU BREAK IT.

Because i did, while i was working out how to help you. lol.

have fun.

[stualk]

Thanks for taking time to do that. I imagine this will work so it could solve a really big problem!!

By doing this and therefore entering all new products inclusive of VAT, is it simple enough to make Actinic deduct the VAT from orders made outside of the EU?

The main reason I need to set the site up this way is from an order processing point of view. We need to see the prices inclusive of VAT when the order reports are printed off.

[Jan]

If you want Actinic to automatically remove VAT for european orders, you need to enter VAT exclusive prices and then let Actinic calculate the VAT. If you need to see VAT inclusive prices on your reports, the simplest solution would be to edit the reports which you can do with crystal reports version 8.5 (or you could get a consultant to do this for you).

Regards,

[stualk]

Excellent, where can I get crystal reports? I was going to ask if it was possible to edit the reports because I want to increase the font size if possible.

On the subject of letting Actinic work out VAT, does anyone know of a work-around for when the VAT calculation skips by 1p? The reason I ask this is because there are products on the website i'm working on that must be £2.99. If you try entering this without VAT you either get £2.98 or £3.00!!

[Jan]

Try this site :

http://www.321db.co.uk/product_info....ucts_id=588643

it is quite an expensive product ATM because it is obsolete.

Regards,

[completerookie]

has anyone tried the version of Crystal Reports that comes with the Microsoft .Net range of products.
taken from the microsoft web site:

Crystal Reports for Visual Studio .NET and for Visual Studio 2005 are fully integrated in the Visual Studio .NET and Visual Studio 2005 family of products.

and lower down on the same page:

Developers can use Crystal Reports for Visual Studio .NET or for Visual Studio 2005 to do the following:
Design unlimited reports for use in Visual Studio .NET or Visual Studio 2005 applications by using the integrated Crystal Reports designer.
Integrate the Crystal Decisions server software (Crystal Reports engine used in conjunction with Microsoft ASP.NET server) into server or Web applications.
Integrate the Crystal Reports engine into client Microsoft Windows applications and, at no additional charge ("free runtime"), developers may:
Freely deploy those Visual Studio .NET or Visual Studio 2005 applications in their own organizations. -or-
Redistribute those Visual Studio .NET or Visual Studio 2005 applications outside their organization for use by third parties.

the only reason I ask, is that
PC world sell the the 2005 Standard Edition for JUST £34.98

Reading the blurb, it appears that you can "Design unlimited reports for use in Visual Studio .NET or Visual Studio 2005 applications by using the integrated Crystal Reports designer" - my guess is that it generates the "crystal report" in the same manner as Actinic does - BUT I'M NOT SURE !

I'm not claiming that this is the (cheap) answer, just curious if anyone has tried this route and can they throw any light on the experience.?

kev

[Duncan Rounding]

I doubt that this would work. Actinic reports are designed using CR V8.5 - no other versions will work. Have a search through the forum and you will find several references to this.
Your best solution might be to contact someone like Jan at Mole End off-forum and see if it would be possible to get a quote for the changes you need.

[completerookie]

oh b***er - the thought was a good on though !

[Jan]

It definately won't work, Business Objects rewrote the internal report format between V8.5 and V9, and they are not compatible, the .NET version was introduced in V9.

Regards,

[stualk]

Hi Jan,

Do you know how much it would be to edit the reports so that the printouts display the line items including VAT rather than excluding VAT?

This is important to me as my client needs to enter each product ordered into their till before shipping the orders. For accounting purposes the amount they enter into the till needs to include VAT so at the moment it's a bit of a pain.

Thanks

[Jan]

It would cost £50 which is our minimum charge ... a workaround might be a list of prices that have been VAT'ed next to the till, so that they can just look them up, you could calculate them for 1,2, 5 quantities etc.

Eek, I am being attacked by an enormous spider, I have to run away now :-(

Regards,

[stualk]

Hi Jan,

£50 is fine. How do I go about buying this then? I see what you mean about the workaround but it would be easier if we could just have each line item displaying the price inclusive of VAT, hence the reason I'd like to buy the reports editor.

Thanks

[Jan]

Just email the full details to me, which reports need to be changed, which fields on the reports need to be made, you can print a report off and fax it to me if you like.

Regards,