Invoicing/reporting questions

[LPB]

Hello! This is my first post here and I'm hoping someone can help me. I'm trying to figure out the best way to create a delivery note and/or invoice to print when we are shipping products.

In my database I can ship individual parts or finished products. I can also ship multiple lines of an order, and multiple lines from different orders. I've not really used reports before but from what I've read, forms should be used for viewing information on a screen and reports used for printing information off. Is this still the case when I need to be making lots of selections, ie. different orders and line items or should I be creating a form and printing it off?

I would also like to create a similar template to the one I already have in excel which means I will need to have an order numbers box and have the selected order numbers placed there one after the other like so: 2245, 2246, 2247. I have no idea how to do that so any help would be great!

I was envisioning being able to select the relevant order numbers somehow and that would then filter the items available to put on the delivery note (possibly a combo box or something). I have something similar to input invoices currently, but this is based on selecting one order at a time.

I have the following tables:
Parts (need to be able to ship items from this)
Products (need to be able to ship items from this)
Orders (order no, date ordered etc)
Line items (item, qty etc)
Shipments (Order number, item ID, shipment ID, qty shipped, date)

If you can work out what I'm talking about, then any words of wisdom would be great. Otherwise let me know if you need any more information or clarification

 

[CJ_London]

it can certainly be done but you'll need to explain how the process works in a bit more detail. These are the sorts of things we need to know

can one invoice invoice multiple orders?
can an order be part invoiced?
if so is that based on product or quantity or both?
do returns need to be handled i.e. credit note and/or replacement shipment?#
is the invoice based per shipment or multiple shipments?

we will probably also need to see your relationships diagram - I note shipments contains the order number which should not be necessary which flags up potential issues with your design

 

[The_Doc_Man]

A crucial part of designing a system or an addition to a system is to analyze it first. You must remember that you are in essence building a database model of your real-world business flow, so whatever you put into the model has to represent reality. And, of course, vice-versa in that you shouldn't implement that which isn't true to reality.

CJ's questions are merely illustrative of the need to explore what you really wanted to be able to do. You need to make a list (for yourself first) so that you can answer questions about what you need to include, what is a realistic goal, and what is an ideal that might or might not be attainable.

Now let's get real: We won't be able to help you get anywhere. We can certainly offer our expertise in putting together models of your business, but we don't work there. We don't know your rules and what you & your bosses need. This forum isn't the medium through which we would learn those rules. YOU are the subject-matter expert. We are at best side-line advisors.

So here is MY side-line advice: When you are in the design phase of your project, that is when you are most likely to make crippling mistakes through haste. Crippling in that making a decision that was not completely examined might lead to a design that forever blows your chance to reach your goal. I know this sounds way more daunting than you might have hoped, but here's the good news. This is, or should be, the SLOWEST part of your project. Once you have a good design for which you have some degree of confidence in its accuracy, things can move VERY quickly.

For the design of any major database, "slow and steady wins the race." Therefore, I will trot out a couple of my Old Programmer rules:

#1: If you can't do it on paper, you can't do it in Access.

This simply means that YOU, not Access, are the expert in your business. You need to make some sort of document, diagram, flow chart, process chart, SOMETHING that will tell you that at point X in your data processing sequence, you need to do steps A, B, and C in that order. You are not ready to start full implementation until you have this detailed model mapped out. (It is OK to do experimental code to test an idea, though.) The point of this is that you are building a roadmap. Because if you don't have a map, how will you ever know with certainty that you are approaching your end goal?

#2: Access won't tell you anything you didn't tell it first.

Access is a data management tool. It manipulates, but does not necessarily CREATE any information. Therefore, take away from this the idea that if you wanted to see XYZ among your outputs (which you determined during application of rule #1), you must either assure that XYZ is in your input, or that X, Y, and Z are separately input and you tell Access how to blend them into XYZ. This might even mean that as you go through the outputs, work backwards through the design to see where your inputs originate.

#3: When the computer and reality do not agree, the computer is wrong.

I touched on this earlier. It is a corollary of #1 in that if your model doesn't correctly map what you do, you WILL NOT get to where you want to go. You will have to seriously "bang on it to make it fit." Though "Percussive Engineering" might be highly satisfying, computers tend to break when you hit them hard. So don't build square pegs when you KNOW you have round holes to fit them in. Which is why you make all those decisions up front.