Invoice Form Question

[Jaybird]

I've created an Invoice form which seems to work pretty well for creating
invoices, but now the Office folks would like to add some functionality.
Right now, we track our orders by Order Number. The Order Entry table
includes information that is unique to the order as a whole. There is also
an associated Order Details table which includes information specific the
line item for a specific order. The Part Numbers table includes information
that is standard to the Part Number. Right now the Invoice information is
included on the Order Entry table. My thinking was that the relationship of
invoices to order was one to one. I have since learned that it isn't so and
that sometimes the same order for the same purchase order is invoiced more
than once to correct billing errors. So, I've decided that I need an Invoice
table. On it would be my Invoice Number as a Primary Key, my Batch Number,
the customer PO, the Invoice Date, the Total Amount Owed and the Total Amount
Paid. I don't want to get blind sided again, so I was hoping some of you
could critique my plan of action and maybe give me some suggestions that will
anticipate future added functionality. I don't work in the office, so I'm
not sure what they are going to want. Anyhow, your input will be appreciated.

 

[Erez Mor]

hi there
it seems you've got a good plan in your hands: the "standard" structure for
orders/invoices is:
Orders table (ordID as Primary key, dates customers etc...)
OrdDetails table (odetID as Primary key, ordID as foreign key, products
included in that order and possibly their qty and price)
Invoice table (invID as Primary key, ordID as foreign key)

Orders and ordDetails has one to many relationship
Orders and Invoice has one to many relationship

good luck
Erez.

 

[Jaybird]

Thank you very much for your response! It's gratifying to know that I'm
making progress here and useful to know what "standard" set ups exist. I'm
self taught (apart from the excellent advice I get here), so I hate to
re-invent the wheel every time I start a project.

 

[Jaybird]

Well, it looks like I could use an Invoice Details table as well, to store
all line items associated with a particular Purchase Order... or maybe not.
Comments?

 

[Jaybird]

I've run into an issue that I am having problems resolving...

As you say, I have an Orders table, and Order Details table, an an Invoice
table. The way I conceived it, the Order Details are related to the Order
Details by the Order Number field. It is the PK in the Orders table and the
FK in the Order Details table. This way, I can keep track of all my orders
and the Parts, quantities, etc. in the Order Details table by means of a
subform in a form I call Order Entry. Similarly, I would like to track all
the orders and the invoices associated with a particular Purchase Order. The
primary key in my Invoice table is the Customer Purchase Order Number, so
that each purchase order can have many invoices (well, more than one at
times). Currently, I have a form based on my Invoice table. I embedded a
subform based on information from both the Order Details table and the
Invoice table. Well, I'm having trouble getting the information to be
updatable. I thought that perhaps my relationships were faulty, as the
Invoice table is related to both the Orders table by the Purchase Order field
and to the Order Details table by the Order Number field. Now I wonder if
it's simply the way I constructed my form. Perhaps the thing to do is to
base my main form on a query that restricts the records from the Orders table
include only the Purcase Order number and relate the Invoices and the Orders
by means of subforms...? The problem I have now is that since the invoice
table doesn't contain the purchase order information until I input it, no
matching records show up in my subform.