Rel is a bad choice for a key. Primary keys must be unique, and you can't guarantee uniqueness of a number or code that you yourself don't even assign. Besides that, different customers might duplicate each others' Rel values. If your in-house PO numbers are unique, that would be a better key. Invoice # might be a candidate, too.
One thing I'm certain of is that you need a child table for the order details Qty, Descript, OurCost, SellPrice, and Freight. This table should have a key consisting of the current table's key and an item number. The item number is just an arbitrary number that you can use to put the order items into a fixed sequence. You can probably make it an Autonumber field, so you don't have to worry about writing VBA code to give it a value.
Having the child table will solve two problems for you. One, you won't be limited to a maximum of 4 items per order. Even if that may seem sufficient at present, business has a habit of changing the rules on you. With the child table, you'll be able to adapt to such rule changes easily. Two, there's no need to store information for a second, third, or fourth item if the order only has one item, so you won't have a problem with 0 quantities on your invoice report.
It seems to me there is an opportunity to do additional normalization on your table. For example, you have order fields (Date, TakenBy), invoice fields (Invoice#, ShippingAddress), product fields (Descript), and customer fields (Customer). Products and customers are often extracted into separate master tables so their information doesn't have to be entered separately for every order. Orders and invoices are often in separate tables because they are actually created at different times.
Unfortunately, I would have to know a lot more about your business and internal procedures before I could make any recommendations about further normalization. My lack of knowledge is so complete that I don't even know what questions to begin with. I can only suggest to you that you do some thinking about whether it makes sense in your business environment to break out this information to separate tables. For example, if you want to keep historical information about orders and invoices in this database for some time after the invoicing is completed, breaking the data into multiple tables may not be a good idea, because changing, say, a price on a product will change it on all existing invoices--even those which have already been paid but are still in the database for reference purposes. So give it some thought, but in an inventory/invoicing application you shouldn't be too quick to normalize.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
