I'm looking for a shortcut from orders --> delivery notes --> invoices

[jt196]

(I'll first preface this by saying that I'm about a month into Access - albeit a pretty frantic one)

OK so my setup at the moment is that I have three tables to manage the first two of the above.

T_Orders

T_DeliveryNotes

T_Transactions

An order goes on T_Orders, and its items go on T_Transactions (with a figure against Quantity_ordered). A delivery note is raised and the items go on the same table with a figure against Quantity_delivered (saving a little time with a combo box taking from Query_OutstandingOrderItems).

I'm also thinking about importing the invoice system from sage and adding a third layer to things.

Now my question is: is there any way I can avoid repeating entry of items - especially on large orders. Someone puts through an order with 20 different products on, and in my current system I have to enter those 20 different products 3 times in order to produce orders, delivery notes and invoices.

Can anyone recommend me a system to speed this process up. ie some kind of macro for me to just shortcut process an order all the way through from Order to Invoice stage. (sometimes we'll get an order through on the phone and just pack it up and send it off right away along with the invoice)

Thanks in advance!

[Jim Doherty]

@jt196
Hi.

If your system is bespoke designed for your own particular business needs then you can pretty much make it do whatever it is you want. The goal with any system is to prevent duplication of entry and make things as seamless as possible. If the data is already in there, then make the computer deal with it. This however, is always going to be down to the developer.

If you are are totally 'au fait' with doing this yourself then 'great' that is what becoming an Access developer is all about. If not, then the term 'employ' someone springs to mind. The 'easy macro' thing sounds fine but is it doing the right and proper thing in any given circumstance is the test question.

Copying table data from one table to another, to another, to another to satisfy some notion of functionality (ie:products table to products ordered table to delivery notes to invoices) might sound a reasonable assumption to the untrained eye, but is plainly inefficient given SQL can query your data based on logic and how table data is linked together.

It is you who must apply the logic to your system if ultimate control is what you seek. How! you do it is always the acid test based on what it is you have there.

I know this sounds more like some kind of lecture on philosophy or something and I don't intend it to be that way but, rather than a simple answer the 'repeated' data entry portion of your question prompted me to think that there may well be some design considerations to be taken on board.

[jt196]

Hmm well I'm not totally au fait with doing this myself but I'm learning a lot in the process. In terms of employing a developer, I certainly have entertained the thought of this - but then I'd miss out on all the fun, wouldn't I? Seriously though, learning Access through all this is going to give me so much more power and control of the bespoke system I'm setting up. It's a bit of a pain in the arse in the beginning, but I have to say, I'm a fast learner and I've gone from zero to a fairly decent level in a short space of time. And this is a life-skill that I'd like to use down the line for other projects.

As for copying data from one table to another - that is not happening. There is one table that manages stock items, sales, orders and invoices - the differences being that different fields are populated by quantities depending on whether it is a stock in item, delivery note out item, order in or invoice out.

My big problem was that plenty of our orders only get partially fulfilled, so it was going to be hard for me to keep track of orders using our current system (Sage - where a delivery note and an invoice are the same thing - and when an order gets part shipped out one week, and then another the following - or maybe a half a van a few days later)... so I needed something that would tell me exactly what goes in and out of our factory but to account for the fact that 500 would go here, 500 there until 2500 of an order would be completed. Now I can track how much of an order went out on that day or that day - whereas with a non-duplication of items, what would I do? Make a form to deduct quantities from an order? Then how would I be able to tell how much of something went out on whatever day?

It's actually all working now - but I guess I was looking for some practical notes as to how I could manage to incorporate the invoices into the system. The suggestion I've had so far is to populate a subform with the data from the order - using that information as the data source... Not sure how that's going to work but it could be worth a try.

Thanks for the pep talk though - always good to get feedback from those with more experience than me!