Northbank Trading

Queries

The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.

Order totals

Every order with its customer, status and the value of its goods after discounts, the newest first.

From Orders, Companies, Order Lines. Shows Order, Order Date, Customer, Status, Goods, with totals.

Calculated: Goods is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Sales by month

Sales and units for each month, cancelled orders left out.

From Orders, Order Lines. Shows Month, Sales, Units, with totals.

Calculated: Month is Format([Order Date], "yyyy-mm"); Sales is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Only where Status <> "Cancelled".

Sales by category

What each product group has sold, the biggest first.

From Order Lines, Orders, Products, Categories. Shows Category, Sales, Units, with totals.

Calculated: Sales is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Only where Status <> "Cancelled".

Sales by employee

What each sales person has sold.

From Orders, Employees, Order Lines. Shows Employee, Sales, with totals.

Calculated: Employee is [First Name] & " " & [Last Name]; Sales is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Only where Status <> "Cancelled".

Top customers

Customers by what they have bought, with their country.

From Companies, Orders, Order Lines. Shows Customer, Country, Sales, with totals.

Calculated: Sales is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Only where Status <> "Cancelled".

Products to reorder

Products at or below their reorder level, by supplier, with how many to order. Discontinued lines are left out.

From Products, Companies. Shows Supplier, Code, Product, Units in Stock, Reorder Level, Reorder Quantity.

Only where Above Reorder Level <= 0 and Discontinued = No.

Orders to ship

Orders not shipped yet, oldest first, with the days left before the customer needs them.

From Orders, Companies. Shows Order, Order Date, Customer, Required Date, Days Left.

Calculated: Days Left is DateDiff("d", Date(), [Required Date]).

Only where Shipped Date Is Null and Status <> "Cancelled".

Unpaid invoices

Invoices not yet paid, by due date, with how many days each is overdue (nought when it is not due yet).

From Invoices, Orders, Companies. Shows Invoice, Customer, Invoice Date, Due Date, Amount, Days Overdue.

Calculated: Days Overdue is IIf(DateDiff("d", [Due Date], Date()) > 0, DateDiff("d", [Due Date], Date()), 0).

Only where Paid On Is Null.

Stock value

Units in stock and their value at cost, by product group.

From Products, Categories. Shows Category, Units, Value at Cost, with totals.

Calculated: Value at Cost is Round([Units in Stock] * [Cost Price], 2).

Product list

The products you still sell, by group and code, with their list prices.

From Products, Categories. Shows Category, Code, Product, Unit, List Price.

Only where Discontinued = No.

Purchases on order

Purchase orders sent and not yet received, by the day they are expected.

From Purchase Orders, Companies. Shows Purchase Order, Supplier, Ordered On, Expected, Status.

Only where Status = Submitted.

Forms

Every table has a form of the same name: Start Here, Business, Companies, Contacts, Employees, Orders, Order Lines, Categories, Products, Purchase Orders, Purchase Lines, Stock Movements, Invoices. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.

Reports

Pages to print or save as a PDF, laid out from a query or a table.

Product catalogue

The price list: every product you still sell by group, with its unit and list price.

From the query Product list, a group for each Category: Code, Product, Unit, List Price.

Monthly sales

One line a month with units and sales, and the totals.

From the query Sales by month: Month, Units, Sales, with the total of Sales, total of Units.

Orders by customer

Each customer's orders, the newest first, with a total for each customer.

From the query Order totals, a group for each Customer: Order, Order Date, Status, Goods, with the total of Goods.

Invoices due

Unpaid invoices by customer with their due dates, days overdue and a total owed: the list for chasing payment.

From the query Unpaid invoices, a group for each Customer: Invoice, Invoice Date, Due Date, Days Overdue, Amount, with the total of Amount.

Stock to reorder

What to order, on a page for each supplier.

From the query Products to reorder, a group for each Supplier: Code, Product, Units in Stock, Reorder Level, Reorder Quantity.

Start here: the everyday tasks, step by step

The guide to this database printed: each everyday task and its steps, from the Start Here table.

From the table Start Here, a group for each Task: Step, What to do.

Print templates

What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.