Teedee Access Teedee 2010
Create Invoices Table

Open The Table Editor

 Invoice Number While this field is not required (we have the InvoiceID field we can use for this) it allows us to allocate out own number. Despite the field name being InvoiceNUMBER this field would be more flexible as a TEXT field. This would allow us to use numbers like: 09/001, 09-001 or DN001. If you use this as a text field you may have a problem sorting by InvoiceNumber as text sorts result in a different order from numeric sort. The following is a text sort result: DN1, DN10, DN11, DN2, DN20, DN21 To get a more consistent sort you would always use the same number of characters in the numeric part, for example: DN001, DN002, DN010, DN011, DN020, DN021. (Tehere are always three numeric characters in the invoice number. If you change this field to TEXT be aware that it MUST (repeat MUST) also be TEXT in tblInvoicesItems.

Tables: Invoices & Invoices Items

We will create two tables tblInvoices & tblInvoicesItems, which we will link in the next session
1 2
Invoices Table (tblInvoices) InvoiceID Autonumber Primary Key Field Size: Long Integer New Values: Increment Indexed: Yes (No duplicates) InvoiceNumber Number Field Size: Long Integer Required: Yes Allow Zero Length: No Indexed: Yes (No Duplicates) InvoiceDate Date Default Value: =Date() Required: No Indexed: No Terms Number Field Size: Integer Default Value: 14 Required: No Indexed: No DueDate Date Required: No Indexed: No PurchaseOrder Text Field Size: 50 Required: No Allow Zero Length: Yes Indexed: No BillTo Number Field Size: Long Integer Required: No Indexed: Yes (Duplicates OK) SubTotal Number Field Size: Single (Format: Currency) Default Value: 0 Required: No Indexed: No GST Number Field Size: Single (Format: Currency) Default Value: 0 Required: No Indexed: No InvoiceTotal Number Field Size: Single (Format: Currency) Default Value: 0 Required: No Comment Text Field Size: 255 (Max permitted) Required: No Allow Zero Length: Yes
Invoices Table (tblInvoices) ItemID Autonumber Primary Key Field Size: Long Integer New Values: Increment Indexed: Yes (No duplicates) InvoiceNumber Number Field Size: Long Integer Required: Yes Allow Zero Length: No Indexed: Yes (Duplicates OK) ItemDate Date Default Value: =Date() Required: No Indexed: No Terms Number Field Size: Integer Default Value: 14 Required: No Indexed: No Description Text Field Size: 255 Required: No Allow Zero Length: Yes Indexed: No Quantity Number Field Size: Single Decimal Places: 2 Default Value: 1 Required: No Indexed: No Rate Number Field Size: Single Format: Currency Decimal Places: 2 Default Value: 1 Required: No Indexed: No SubTot Number Field Size: Single Format: Currency Decimal Places: 2 Default Value: <Your hourly>  Required: No Indexed: No GST Number Field Size: Single (Format: Currency) Default Value: Required: No Indexed: No InvoiceTotal Number Field Size: Single (Format: Currency) Default Value: 0 Required: No
3 4

Currency

For the fields SubTotal, GST & InvoiceTotal you set the Data Type: Currency.

Terms

Even though we have a Terms field for the Customer we require a Terms field for the invoice. The Customer's Terms are used as the default value for the Invoice. However, you may over-ride the Terms on the invoice. You may allow a Customer 30 days normally but on an individual invoice you are invoicing for goods purchased for cash. You want payment in, say, 14 days.
Home Tutorial Code Snippets