Creating a basic invoice system:

1>                Set up a table structure by creating three tables as shown below and then setting the relationships. In each case the first ID field of each table is an autonumber field and is set as the Primary field.

2>                You create the relationships through Tools è Relationships.  Then drag and drop the fields you want to relate between the tables.

3>                Create a customer form from the customer table and add two or three customers

4>                Create a query using the Sales and Customer table. include all of the sales fields and also all of the Customer fields EXCEPT CustID. Save and close your query.

5>                Create a form based on this query, include all of the fields on the form.

6>                Add a combo box based on the customer table so that you can select customers from this.

7>                Select the Title, Name, Address, Town and County fields on the form, go to properties è Dataè Locked and set this to YES.  This means it can only be updated from the drop down combo box.

8>                To make the salesID look more like an invoice number select it è properties è FormatèFormat  and enter 00000. This will make the sales id come out in the form 00001 rather than 1.

9>                Adjust the layout as you want and save the form.

10>            Create a Query from the Items table. Include all of the fields and add one custom field:     total: [Unitcost]*[Quantity]

11>            Save your query and then create a form based  on this. Initially add ALL the fields onto the form from the query. The in design view you can delete the ItemsID and salesID entries. (They only need  to be there for the creation of the form - they don’t need to be on the form itself)

12>            Select the whole form  - [Ctrl] + R - propertiesè Default view => datasheet. Record selectors => Yes, navigation Buttons => No  Save and close the form

13>            Open the sales form (created in 8 and 9 above) in design view.

14>            Click on "Windows" from the menu and select item 1.  ….database.

15>            From the database window  under forms, drag the form you created in 11 and 12 and drop it on to the sales form -  this is your subform.

16>            Immediately click on the properties button to display the properties for this subform èData è Link Child Fields è click on the little button and in the next form it should have salesID in the Master and child fields. Ok and close it.

17>            Adjust layouts, width etc. save your form. open it and start to enter invoices

The Invoice:

1>    Create a query. Start with the Items table and add Description, Quantity and unit Price, add the custom field Total: [Unitcost]*[Quantity] . Now add the Sales table and include SalesID and SaleDate, now add the customer table and include all fields EXCEPT custID.

2>    Under SalesID in criteria add the following  [Forms]![sales]![SalesID]   -  this creates the link back to our form

3>    save the query.

4>    Go to reportsè Create a report based on this query. Put all of the fields on and next, when prompted by  "How do you want to view data?"  select  BY Sales. This will create the necessary layout.  Ignore the grouping and when you get to layout choose Left Align 1

5>    Finish off the report. NOTE before opening it make sure the sales form is open, otherwise it has nothing to link to.

6>    Go into design mode for the report and play around with the layout.

7>    In particular: select the grouping button and turn on the salesID footer.

8>    In the SalesId footer add the following as needed. This will give you your various totals.

9>    Save your report.

Linking from the sales form:

1>                Open your sales form in design view. From the toolbox bar make sure that the wizard is on, add a button in the form header, from the window that pops up select Reports, Preview è next select your report è next add a caption for your button and finish.

2>                Add another button, this time select record operations and save record. Again give it a caption in the appropriate window and finish.

You can now create your invoice, save it and print it.  If you don’t save before printing Access has not had a chance to update and so you wont get all the data in it.

Once you have this basic structure you can start to get more complex and use product tables for selecting your products  ( see earlier weeks notes)

The secret of working with access is start simple, make sure that works, then get more complex.