Hi all
So I have an invoicing and orders and dispatch system for my manufacturing company which I designed in Excel and it works fine. The problem is that the company has grown to such an extent that I now need multiple users to be inputting data into the database simultaneously which Excel won't allow without overwriting some of the other users' data. In addition I am told that Access is a far more powerful and suitable application for my needs. The downside here is that I have no experience whatsoever with Access with the exception of a few beginner tutorials on YouTube.
My initial impressions of Access so far are that it seems structurally very different from Excel and so am hoping for a few pointers from more experienced users to smooth the path to proficiency.
The structure of my system is as follows:
- The company manufactures a range of bespoke products. Very few of the items are standard stock so compiling a database of standard products is not the way to go here. In my Excel system I have a spreadsheet where the user inputs the bespoke size of the item ordered L x W x H and the material it is to be manufactured in, a formula then calculates the price of each item based on these values. Each order and the items within it are unique.
- Typically there are several different items per order so I need to be able to input multiple records (sometimes up to 300+) for each order. This is easy in Excel as the user keeps populating lower rows on the worksheet but from what I have seen of Access forms it seems only one at a time can be entered and the previous ones are not visible when entering a new one.
- Each order will have some header data such as: unique reference number, client name, order date, price, estimated delivery date, staff member who took the order etc. This needs to be stored in a database which then leads to a more detailed record of each order if any of the records are clicked i.e. show the full details of every item ordered.
My initial questions are:
1) Can Access calculate the price of each item based on real time input of dimensions, material etc in the same way that Excel can?
2) Is there a way of entering an unlimited number of items into an order all on one sheet without using a new form for each record?
3) What tables do I need? I assume Products, Clients and Orders are the obvious starting three.
I am intermediate with VBA so I'm not afraid to add some if required.
I am running Access 2007 on a PC with Windows 7.
Any assistance much appreciated.
Rich
So I have an invoicing and orders and dispatch system for my manufacturing company which I designed in Excel and it works fine. The problem is that the company has grown to such an extent that I now need multiple users to be inputting data into the database simultaneously which Excel won't allow without overwriting some of the other users' data. In addition I am told that Access is a far more powerful and suitable application for my needs. The downside here is that I have no experience whatsoever with Access with the exception of a few beginner tutorials on YouTube.
My initial impressions of Access so far are that it seems structurally very different from Excel and so am hoping for a few pointers from more experienced users to smooth the path to proficiency.
The structure of my system is as follows:
- The company manufactures a range of bespoke products. Very few of the items are standard stock so compiling a database of standard products is not the way to go here. In my Excel system I have a spreadsheet where the user inputs the bespoke size of the item ordered L x W x H and the material it is to be manufactured in, a formula then calculates the price of each item based on these values. Each order and the items within it are unique.
- Typically there are several different items per order so I need to be able to input multiple records (sometimes up to 300+) for each order. This is easy in Excel as the user keeps populating lower rows on the worksheet but from what I have seen of Access forms it seems only one at a time can be entered and the previous ones are not visible when entering a new one.
- Each order will have some header data such as: unique reference number, client name, order date, price, estimated delivery date, staff member who took the order etc. This needs to be stored in a database which then leads to a more detailed record of each order if any of the records are clicked i.e. show the full details of every item ordered.
My initial questions are:
1) Can Access calculate the price of each item based on real time input of dimensions, material etc in the same way that Excel can?
2) Is there a way of entering an unlimited number of items into an order all on one sheet without using a new form for each record?
3) What tables do I need? I assume Products, Clients and Orders are the obvious starting three.
I am intermediate with VBA so I'm not afraid to add some if required.
I am running Access 2007 on a PC with Windows 7.
Any assistance much appreciated.
Rich