Some n00b DB questions.

[Mars]

I am learning Access. After all the crap I'm having with excel its time to convert the damn thing into a database. I already own Access 2007 so I'm learning that, and with the Access runtimes I will be able to run it on all the other machines without having to buy more licenses.

The orginial excel file was originally designed to do two basic things. Check the stock on a daily basis and do the cash up. Our accounting package cannot accurately calculate stock because we also supply stock on behalf of our supplier that does not get invoiced (by us).

Then it grew in to a system to check that cash was not being stolen by tying each invoice to a payment method and to specific stock. Then sales figures where added to allow for accurate ordering. At the end of each day a print-out is made showing the stock levels, the invoices for the day,the stock supplied on behalf of our supplier and the cash-up. Our cashier then signs the copy and I sign receipt of the daily cash as well as that I have checked that the stock is accurate.
Then I added a sheet to show what stock has been ordered for that month.
Now its a monster.
I have one of these files for each month stretching back for the last 5 years. It was working fine until recently it started to get corrupted and refused to save. So thats an issue. Re-entering data and printing reports days after the fact has given the opportunity for stock to go missing. Thing is I can't be 100% sure the stock is missing because the records cannot be trusted anymore. I have to go back and re-enter 8 days of data to find out. I'm sure its just a missing invoice or a typo somewhere but that the opportunity is there pisses me off.

So, its convert the bugger into a database.
Now knowing only what I have managed to learn from a Lynda.com access course I have a few questions.
1. If I update my cost price will it affect the records I have already entered?
Up till now this has never been an issue because each month carries its own cost. The database would run for a year(?) and it would mess up everything if it changed all the records.

2. How long would I be able to run the database before starting a new one? One for each year? The finances are done in pastel, so this has nothing really to do with that other than making sure that no pilfering is going on. Also DB would not be invoicing. This is done in another package, the invoices are captured manually into it each day.

3. Would I be able to keep my basic info in excel and just make the DB refer to it?

4. Am I biting off more than I can chew?

 

[iblade]

1. Yes. It boils down to design. There are ways around this, if you add dates to your WHERE clause to target only a specific months cost.
2. Depends on the amount of data, Access DB's should be kept under 1GB.
3. You could, but I would suggest using Access for this anyway.
4. No idea mate, that answer is yours.

 

[InTheCube]

Why don't you just use Pastel, or another accounting package? Why try to re-invent the wheel? Fork out for a Pastel license, import all your data, make sure you do regular backups, and sleep easy at night.

 

[Drake2007]

/Rant on
Once you're on Pastel you're locked into using their software forever and you'll find it won't do everything that you want. To make it worse their stuff is just a client end to firebird or some other such database that no one's ever heard of. I really wouldn't recommend their software and support to my worst enemy.
/Rant off

Continue on your path, you will find there's no piece of software that will fit your specific needs.
1. Not if you use a many-to-many relationship to avoid that.
2. You can always move to mySQL or MSSQL at a later stage, be carefull here.
3. You can use Excel as a frontend to Access, I wouldn't recommend the other way round.
4. Depends how big it gets hey..

and with the Access runtimes I will be able to run it on all the other machines without having to buy more licenses

I really don't want to confuse the issue but I see a problem here, maybe consider running mySQL (LAMP) then your client side is any web browser or even Excel (with some vb scripting ) on your LAN, there are PHP accounting solutions out there already. Steep learning curve though.

 

[Mars]

1. Yes. It boils down to design. There are ways around this, if you add dates to your WHERE clause to target only a specific months cost.
2. Depends on the amount of data, Access DB's should be kept under 1GB.
3. You could, but I would suggest using Access for this anyway.
4. No idea mate, that answer is yours.

Lol Thanks for the input. I will use access. I'ma gonna give it ma best shot.

Why don't you just use Pastel, or another accounting package? Why try to re-invent the wheel? Fork out for a Pastel license, import all your data, make sure you do regular backups, and sleep easy at night.

I have the latest Pastel partner. It cannot calculate stock because of a few reasons.
All my invoicing is done through an old system supplied by my franchiser. It only exports to pastel 5 which is incredibly unreliable. We then export the figures to Pastel 10 and do the books. It cannot export invoice for invoice, I have tried.
Whats more, Pastel 5 and 10 cannot be installed on the same network. Seriously. Something in the Database management system vlooks the other one causing the Pastel 5 Machine to bluescreen. Pastel don;t support 5 anymore so they don't care about my kark.

Then. We supply stock on behalf of our supplier. This stock is not processed in the ordinary way. It does not get invoiced by us at all, so Pastel does not see the stock leave. The stock gets replaced so pastel see's more stock coming in. the result is no clue as to whats going on stock wise.

3. Pastel has no way to do a daily cash-up.