Re: Problem Designing Tables
Hi,
Ive designed stuff like thsi and do this all the time. youre looking at something like this.
Invoice
InvoiceNO (auto generated number, NOT NULL, INT 11)
OrderDate (DATETIME NOTNULL) default value (GETDATE(NOW))
ContractNo (VARCHAR 100, NOT NULL)
above could be (BIGINT 100, NOT NULL) if only numberical
Compnay name (VARCHAR 200, NOT NULL)
Address1 (Varchar 100, NOT NULL)
Address2 (VARCHAR 100, NOT NULL)
City (VARCHAR 50, NOT NULL)
State (VARCHAR 50 NOt NULL)
Zip (VARCHAR 50, NOT NULL)
Tel (BIGINT 100, NOT NULL)
CtctName (VARCHAR 100, NOT NULL)
DelAddr1 (VARCHAR 100, ALLOW NULL)
DelAddr2 (VARCHAR 100, ALLOW NULL)
City (VARCHAR, 50, ALLOW NULL)
State (VARCHAR 50 ALLOW NULL)
DelZip (VARCHAR 50, ALLOW NULL)
DelTel (BIGINT 100, ALLOW NULL)
DelCtctNAme ( VARCHAR 100, ALLOW NULL)
This takes care of the delivery and office addresses, now for the itemisation
PartNo (VARCHAR 100, NOT NULL)
above could be again (BIGINT 100, NOT NULL)
Description (VARCHAR 254, NOT NULL)
ItemCount (BIGINT 100, NOT NULL)
Nett (BIGINT 100, NOT NULL)
Gross (BIGINT 100, NOT NULL)
VAT (BIGINT 100, NOT NULL) default value ((NETT/100) * 17.5)
And thats it. this table will now provide any company with a single invoicing reporting system.All youhave to do is write the report layout and code the query
then you have one table that wil manage all the company stock and invoicing details.
If you want payment due dates and such, just go ahead and add them using a Date conversion method that is specific to the database being used for this table to convert the order date to the payment date.
Hope this helps
Kai
Re: Problem Designing Tables
If it's possible to have several deliveries to satisfy an order, you might want to consider more of a "transaction" based table. More flexible (less flat) then what you are suggesting.
Here's a couple of threads that have dealt with issues like this before:
http://www.vbforums.com/showthread.p...stock+quantity
http://www.vbforums.com/showthread.php?t=372988
http://www.vbforums.com/showthread.php?t=311144
They might be more stock/quantity related, but the concept is similar...