Help

[Scott Bell]

From my last thread you will see i am a starter not only with Access but with this forum as well, I am trying to cheat with access to set up a couple of databases, this is because i don't really know what i am doing after being on a course for the damn thing.
I would appreciate a template for a "Sales Invoices" and a "Supplier Invoices " to allow me to track the orders, payments received etc, if i could get a template from somebody it would give me a start, I have had a look at the Northwind Database and maybe it's just me but i don't know where to start.
All help would be gratefully appreciated.

 

[Fuga]

The first thing you need to do is to decide what information you want in the db and how it should be structured. For instance, it sounds to me that you need a "customers table" and a "suppliers table", with whatever information you need. Be sure you have a primary key in the tables.

Since, I guess, one customer can have many "buys" and therefor many invoices, you need a table for this, linked to the Primary Key(ID) in customers table.
etc

This is the way you need to think, I think. If one customer can have many orders, and every order can have many part payments, you need one more hierarchy level.

Fuga.

 

[Scott Bell]

I think i already have this, it's linking it all together where i am struggling, could i e-mail an attachment to yourself to see if i am going in the right direction, Thank you.

 

[Fuga]

I don´t know how much help I´ll be, but I´ll have a look later today.

If you post the attachement here, more people will be able to help you.

I use access 97.

If you have it in a version I cannot open, please tell me more about what you want to do.

Fuga.

 

[Scott Bell]

I use Access 2000, but i have attached a half finished database for you persual, thanks once again.

 

[Scott Bell]

I use Access 2000, but i have attached a half finished database for you persual, thanks once again.

 

[Fuga]

I´m afraid I can´t get it opened.

Perhaps someone else will be able to help. I noticed I´m not the only one who downloaded your sample db.

Fuga.

 

[Scott Bell]

Thnaks for trying, i will just have to wait or try and solve the problem myself, hope somebody helps!!!!
Thank you again.

 

[Mile-O]

I use Access 2000

Goto Tools -> Convert to Access 97

Repost the 97 version


Simple as that...

 

[neileg]

I've opened it. Doesn't really help. There's not enough there to tell what it is you're trying to do.

I would make a couple of points:
You've got two Invoice tables, do you really need this? You should keep all data that is similar in the same place. I guess the only difference between Scottish Power and Engineering invoices is the name of the customer.
Your object names need sorting out. You should use names with spaces in. It is better to identify the nature of the object, too. Like this, perhaps: tblJobRecord, qryPaidInvoces

Perhaps you could give us an overview of what you want this db to do for you.

 

[Mile-O]

You should use names with spaces in.

Surely you mean names without spaces...?

 

[Scott Bell]

I will try and give you a brief overview:
I work for a small engineering company and i am trying to develop a database to manage all purchases from order through to paying the bills and also tracking sales invoices & payment this would eventually tie in with an easy to use job costing system where i tie up invoices, labour expediture etc to each specific contract.
I have tried looking at northwind but would prefer to create my own database as i have just started using Access and only know the basics so Northwind to me is just a bit complicated (MAYBE JUST ME!!!!) I am one of those people that know what i want but can't actually put it into action.
If i can get the basics i can then self learn the rest hopefully.

Regards,

SB

 

[neileg]

Mile, correct as usual.

Thanks for the explanation, Scott. Are you sure you wouldn't rather spend a couple of hundred quid on a simple accounting system that does this all out of the box? I'm a chartered accountant and a moderately experienced db programmer, but I wouldn't want to takle the project you are suggesting! Your system is going to look a lot like Northwind by the time you finish.

I suggest you start simply, say just with purchase ordering. You're going to need tables for the following:
Suppliers
Purchase order header
Purchase order lines
Invoice header
Invoice lines
Payment header
Payment lines

I would forget about the rest till you have something working.

Some thoughts about the business process you are modelling
- You will need to deal with order cancellation and amendment
- Are you going to do price checking between order and invoice
- Are you going to deal with goods receipting
- How are you going to handle discrepancies between the order and the invoice that are accepted by the company. E.g. order 2 tonnes of sand and receive 2.1 tonnes, but that's OK
- How will you deal with unpriced orders, or unquantified orders

Good luck. We'll be hearing from you, a lot!