Invoice Question

[lookup13]

Long story short , I use access 2000 ver9 sr-1
I enter all orders in our data base and now i want to
start billing from there as well.However I ran into a small
problem.. see example


Qty1 Descript1 shipped unit price1 total
Qty2 Descript2 shipped unit price2 total
Qty3 Descript3 shipped unit price3 total
Qty4 Descript4 shipped unit price4 total
Inbound
outbound
SubTotal
Total

This works fine except if there is no valve in
qty2-qty4.If I put zero in , it works but adds
zero in invoice see example

line qty item Description shipped unit price total
1 10 shoe 10 $1.00 $10.00
2 0 0 0 $0.00

Any Ideals?/
Also , what is a good way to auto fill address boxs.
I only run one invoice at time. Not batches

 

[RickSpr]

Use the Section_Format event procedure to test for qty=0. If so, set the MoveLayout and PrintSection properties to False.

See the Help file topic for MoveLayout for more information.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lookup13]

Hi Rick,

I have no info on this Movelayout, My help section is very
vague.Please send info or link on more detail.
Where is the section format ? I am still learning and
have 3 books of 500 pages each.They all give insight on basic stuff , but tend walk off on the above avg stuff.

 

[RickSpr]

I guess you don't have any experience with event procedures, nor with VBA coding. I will try to help, but you'll need to study up on using VBA code and the VB editor.

First, I need to know something. Does the record source for this report have fields [qty1], [qty2], [qty3], [qty4] etc.? Or does it just have [qty] and you're printing multiple records on the invoice? I hope it's the latter. If you have multiple [qty#] fields, your table structure is poor and this will be a lot harder to figure out.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lookup13]

Thanks for you helps, and yes I have a lot to learn
All my fields Qty1-Qty4 and each Decript1-4.
Lets do what ever is the best way.

 

[RickSpr]

Does that mean you're willing to consider restructuring your tables? (It's the process called 'normalization'.)

If so, give me a list of table names you have, and the field names in each one. For any fields important to the invoice, explain what they are unless it's obvious (I have invoicing experience).

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lookup13]

Sorry about delay
Project 3970 (data base on nonstandard items , we dont stock)
Date
invoice #
Invoice
Line (customer code)
Rel (customer Code)
Customer
PO#
Qty1-qty4
Descript 1-4
ourcost1-4
sellprice1-4
Frieght1-4
subtotal
total
Inbound
outbound
net30
duedate
takenby(orderProcessed By)
Then address section
billing address Co. Po Box street city State zip
shipping address Co. Po Box street city State zip
Att
I would like to start 2005 with better Data base system

 

[RickSpr]

I was unable to get back to this today, myself.

I'm afraid I can't decipher everything from just a list of names. What is "Project 3970"--a table name? Column name? Which other names are table names? Why is "Line" tagged as a "customer code"? Same question for "Rel". Plus, why are there apparently 3 customer codes?

Please try again, showing me table names and the names of their columns. While you're at it, please designate which columns are the primary keys of their table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lookup13]

Project 3970 (Table name)
All others are column names
Date
invoice #
Invoice Date
Line (customer code)(this is a code provided by customer)
Rel (customer Code) (we just type it in for ref on invoice)
Customer
PO#
Qty1-qty4
Descript 1-4
ourcost1-4
sellprice1-4
Frieght1-4
subtotal
total
Inbound
outbound
net30
duedate
takenby(orderProcessed By)
Then address section
billing address Co. Po Box street city State zip
shipping address Co. Po Box street city State zip
Att
I Dont have a primary key, but if we need one Rel would be it (this is their ver of PO #) While the other (PO) refers to Our in house PO.
I have several Queries and reports running off of this table
but i will change those to fit our needs.

 

[RickSpr]

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

 

[lookup13]

I see what you are saying, I agree...
When we set this Database up it was done , quickly with out thinking points and future needs. So I decided to change that this year.There are other programs Quickbooks Pro, Peachtree, but the setup on those dont fit our needs, and
to put a Pro database together very $$$. For a small company , simple will do. I had Microsoft show me their
small business software , more book keeping than the features I wanted and really not much different from what I was doing.
So I want to normalize , so we need
Basic info Table
Parts Table
Address Table
All Tied by a primary key (PO#)
As for historical Date, I close out the datebase and save as new year 3970 2004 etc..
Thank you for your help and time.