Question Recomendations on how to set up despatch and invoicing?

[Johnrg]

Guys,

I work at a sawmill and we have a database that we use for our stock and order control.

We have forms to add, ammend and view stock items and customer orders.

I want to develop the database to include multiusers form(s) for despatch and invoicing including automatic order and stock adjustment if possible.

The stock adjustmnet is pretty simple. We would enter in the timber packet numbers into a form that would retreive the descriptive details and tally information and these packet numbers would be changed from true to false when invoiced meaning we have the history of these packets if required.

I am a bit stuck on how to tackle the automatic order adjustmnet.

Our products do not have part numbers or codes as such when we enter them into the system. We choose descriptive details from eight tables and then add a volume ordered, length ordered and a measure ordered.

What we actually despatch more than often does not have the same exact volume, length or measure that is ordered.

I know this is a broad question but can anyone let me know if they have any simple ideas how this could be acheived that I could build on with my programmer?

Thanks

JohnG

 

[gemma-the-husky]

the best thing is not to think about the forms, but to think about the data (although thinking about the forms will help you think about the data) - if the data is structured correctly, then form and report design is made a lot easier.

so you actually need a "despatchtable" to store the details of what you are despatching/selling, and probably use this as a base for the invoices. you may need another "invoice table", or you may store the invoice details in the despatch table. this depends realy on whether you raise invoices for each despatch separately, or include several despatches on a single invoice

now this issue of no product numbers seems really strange. if you are trying to monitor transactions in some particular product then you need to know how much you have bought and sold. it is better to have a product code, than just to use a text description - eg what if you change the text description? one way is to use combo boxes, so you pick a description, but ACTUALLY store a unique code relating to the description - so you can then change the description without causing any problems

there are particular problems in dealing with stock of things like say, carpets or wood - because when you sell something, you may leave yourself with a less useful offcut - so you need a way of being able to deal with all these remnants - i am not sure of the best way, offhand - this is a tricky area.

 

[The_Doc_Man]

Gemma is correct. I will add my spin on this problem, not to correct him but to add another viewpoint that might or might not help you re-orient yourself on what you need to do.

You need to know a couple of "old programmer rules." These are short-hand ways to help you remember things you need to consider.

1. If you cannot do it on paper, you cannot do it in Access. (Meaning, if you don't know how to do this by hand, you won't be able to tell Access how to do it by machine.) Solution: You must start with a study of the problem. More about this later.

2. Access won't tell you anything you didn't tell it first. (Meaning, if you expect to see something in the database, remember to either put the something there in the first place, or at least tell Access how to compute it if it is that type of thing.) Solution: You must seriously think about what you want to get out before you can ever decide what to put in.

Both of these require you to analyze your problem in the mind-set of a business model or simulation, because for the type of problem you describe, that is exactly what you will be doing. Whether you thought of it that way or not. So you need to do a business model analysis to identify what you track in the way of actions, materials, labor, overhead, shrinkage/spillage, etc. Browse the "Theory and Design" section for articles on how to identify business model entities to be tracked.

If you are not yet familiar with normalization, start now before you hopelessly entangle your database. Study normalization BEFORE you lay out your model, because that will help you identify things that in the model can be subdivided vs. things that must be monolithic.

For normalization, use Access Help for their normalization article. Go to Wikipedia.org for an article on Database Normalization. Search the web for the latter topic as well. Remember to include "Database" in the search because there are also mathematical, chemical, social, and diplomatic normalization processes. Read articles until it all looks the same for three articles in a row. You might wish to limit your reading to articles from the .EDU domain, basically from colleges or universities you recognize. If the article looks really short, maybe you should skip it unless it has lots of hyperlinks.

When you are done with the reading and entity analysis, you will probably want to get your Access DB design to at least 3rd normal form.

One of the things you also need to consider is that you will need some sort of identifiers for things you deliver, even if you have to make up something. This is because Access is going to want to build its internal relationships based on pointers to something else. Which cannot happen unless you provide a basis for those pointers. Every table should have a primary key precisely because the PK is one possible basis for external pointers (foreign keys). Part of your business modeling will have to formalize the way you describe whatever it is that you ship.

Do the reading, analyze your problem, and think about how to represent what it is that you do. Come back with more specific questions when you have them.

 

[Johnrg]

Thanks Guys, appreciate your help and input.
JohnG