General Overview Newbie Question (1 Viewer)

Rich_B

Access Newbie
Local time
Today, 15:39
Joined
Feb 12, 2016
Messages
50
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:39
Joined
Aug 30, 2003
Messages
36,118
I only have a minute but:

1) Probably yes

2 & 3) Yes. You'd have another table, order details. The orders table would have general info like customer, date of order and an order ID. The details table would have fields for the order ID, product, quantity, price, etc. Those 2 tables are related by the order ID. You'd use a form and subform for the user. The form would be bound to the orders table, the subform to the details table. Master/child links would keep the subform in sync with the form. The subform would be in continuous or datasheet view, which display multiple records.

Off to play racquetball, but somebody will likely come along with more detail.
 

Rich_B

Access Newbie
Local time
Today, 15:39
Joined
Feb 12, 2016
Messages
50
I only have a minute but:

1) Probably yes

2 & 3) Yes. You'd have another table, order details. The orders table would have general info like customer, date of order and an order ID. The details table would have fields for the order ID, product, quantity, price, etc. Those 2 tables are related by the order ID. You'd use a form and subform for the user. The form would be bound to the orders table, the subform to the details table. Master/child links would keep the subform in sync with the form. The subform would be in continuous or datasheet view, which display multiple records.

Off to play racquetball, but somebody will likely come along with more detail.

Hi Paul

Thank you for your input. That's an encouraging start and I've already learnt a couple of new things there regarding subforms and master/child links.

The mountain already seems more climbable. :)

Any further advice appreciated.

Enjoy the raquetball.

Rich
 

Users who are viewing this thread

Top Bottom