Granite and Grain Woodworks

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.

Costed bill of materials

Every material in every product, with how much it takes, the unit cost and the line cost. Use it to check a price or to see what a price rise in one timber does to each product.

From Products, Bill of Materials, Materials. Shows Product Code, Product, Material, Quantity, Unit, Unit Cost, Line Cost.

Calculated: Line Cost is Round([Bill of Materials].[Quantity] * [Unit Cost], 2).

Only where Active = Yes.

Product margins

Each product's price less its material cost and its labour, with the margin as a percentage of the price, the best first. Labour is costed at 21 an hour; change the number in the query to your own rate.

From Products. Shows Code, Product, Category, Price, Material Cost, Labour Cost, Margin, Margin %.

Calculated: Labour Cost is Round([Labour Minutes] / 60 * 21, 2); Margin is Round([Price] - [Material Cost] - [Labour Minutes] / 60 * 21, 2); Margin % is Round(100 * ([Price] - [Material Cost] - [Labour Minutes] / 60 * 21) / [Price], 1).

Only where Active = Yes.

Materials for planned work

What the planned work orders need of each material, from their bills of materials, beside what is allocated and in stock. The Needed column is how the Allocated figure is made up.

From Materials, Bill of Materials, Products, Work Orders. Shows Code, Material, Unit, Needed, Allocated, In Stock, with totals.

Calculated: Needed is Round([Work Orders].[Quantity] * [Bill of Materials].[Quantity], 2).

Only where Status = Planned.

Material shortages

Materials where the planned work needs more than is in stock and on order, with how much is short. Buy these before the work that needs them is due to start, or it will wait.

From Materials, Suppliers. Shows Supplier, Code, Material, Unit, Allocated, In Stock, On Order, Short By.

Calculated: Short By is Round([Allocated] - [In Stock] - [On Order], 2).

Only where Short By > 0.

Materials to reorder

Materials whose free stock (in stock, plus on order, less allocated to planned work) is at or below the reorder level, by supplier, with the minimum order for that supplier and how much you usually order. Free stock below nought is a shortage.

From Materials, Suppliers. Shows Supplier, Minimum Order, Code, Material, Unit, Free Stock, Reorder Level, Reorder Quantity.

Calculated: Free Stock is Round([In Stock] + [On Order] - [Allocated], 2).

Only where Above Reorder Level <= 0.

Purchases on order

Orders placed with suppliers that have not arrived, with when each is expected and what it is worth.

From Material Purchases, Suppliers, Purchase Lines. Shows Purchase, Supplier, Ordered On, Expected, Value, with totals.

Calculated: Value is Round([Quantity] * [Unit Cost], 2).

Only where Status = Ordered.

Open work orders

Work planned or in progress, the soonest due first, with the maker, the order it is for and the days left. Days left below nought means it is late.

From Work Orders, Products, Makers. Shows Work Order, Product, Quantity, Maker, For Order, Planned Start, Due Date, Started, Status, Days Left.

Calculated: Maker is [First Name] & " " & [Last Name]; Days Left is DateDiff("d", Date(), [Due Date]).

Only where Status In ("Planned", "In progress").

Work order costs

Work orders finished in the last 90 days, with the hours booked against the standard hours, the labour cost at each maker's rate and the material cost.

From Time Sheets, Makers, Work Orders, Products. Shows Work Order, Product, Quantity, Finished, Standard Hours, Hours Booked, Labour Cost, Material Cost, with totals.

Calculated: Standard Hours is Round([Work Orders].[Quantity] * [Labour Minutes] / 60, 2); Labour Cost is Round([Hours] * [Hourly Cost], 2); Material Cost is Round([Work Orders].[Quantity] * [Material Cost], 2).

Only where Status = Done and Days Since Between 0 And 90.

Maker hours by month

The hours each maker booked to work orders in each month, and what they cost.

From Time Sheets, Makers. Shows Maker, Month, Hours, Cost, with totals.

Calculated: Maker is [First Name] & " " & [Last Name]; Month is Format([Worked On], "yyyy-mm"); Cost is Round([Hours] * [Hourly Cost], 2).

Maker hours, last 4 weeks

The hours each maker booked over the last four weeks, to see who is busy and who has time.

From Time Sheets, Makers. Shows Maker, Role, Hours, Cost, with totals.

Calculated: Maker is [First Name] & " " & [Last Name]; Cost is Round([Hours] * [Hourly Cost], 2).

Only where Days Ago Between 0 And 27.

Orders to deliver

Open orders due in the next 14 days, and any already late, the soonest first, with what the goods come to.

From Sales Orders, Customers, Sales Lines. Shows Order, Customer, Kind, Order Date, Delivery Due, Days Left, Goods, with totals.

Calculated: Days Left is DateDiff("d", Date(), [Delivery Due]); Goods is Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).

Only where Days Left <= 14 and Status = Open.

Late deliveries

Orders delivered after the promised day in the last year, with how many days late.

From Sales Orders, Customers. Shows Order, Customer, Kind, Delivery Due, Delivered On, Days Late.

Calculated: Days Late is DateDiff("d", [Delivery Due], [Delivered On]).

Only where Days Late > 0 and Days Ago Between 0 And 365.

Unpaid invoices

Invoices for orders delivered and not yet paid, the oldest due date first, with the days overdue. Below nought means it is not due yet.

From Sales Orders, Customers. Shows Invoice No, Order, Customer, Kind, Delivered On, Invoice Due, Invoice Amount, Days Overdue.

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

Only where Status = Delivered and Paid On Is Null.

Finished goods in stock

Products on the shelf, with what they are worth at cost and at price.

From Products. Shows Category, Code, Product, In Stock, Stock Level, Value at Cost, Value at Price.

Calculated: Value at Cost is Round([In Stock] * ([Material Cost] + [Labour Minutes] / 60 * 21), 2); Value at Price is Round([In Stock] * [Price], 2).

Only where In Stock > 0.

Sales by month

Goods delivered in each month, after discounts and without delivery fees.

From Sales Orders, Sales Lines. Shows Month, Items, Sales, with totals.

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

Only where Status = Delivered.

Sales by product

What each product has sold, the biggest seller first.

From Sales Lines, Products, Sales Orders. Shows Category, Product, Items, Sales, with totals.

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

Only where Status = Delivered.

Sales by customer

What each customer has bought, the biggest first, with how many order lines.

From Customers, Sales Orders, Sales Lines. Shows Customer, Kind, Order Lines, Sales, with totals.

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

Only where Status = Delivered.

Quotes awaiting an answer

Quotes sent and not yet accepted or declined, the oldest first, with the days left before their prices run out. Ring these customers.

From Quotes, Customers. Shows Quote No, Customer, Kind, Quote Date, Valid Until, Days Left, Total.

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

Only where Status = Sent.

Quote results by month

How many quotes were sent each month and what became of them, with their value before VAT: how many turn into work.

From Quotes. Shows Month, Status, Quotes, Value, with totals.

Calculated: Month is Format([Quote Date], "yyyy-mm").

Forms

Every table has a form of the same name: Start Here, Business, Suppliers, Material Purchases, Materials, Purchase Lines, Material Movements, Products, Bill of Materials, Makers, Customers, Sales Orders, Sales Lines, Quotes, Quote Lines, Time Sheets, Work Orders, Work Order Materials, Stock Movements. 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.

Costed bill of materials

A page for each product listing its materials and their cost, with the total material cost.

From the query Costed bill of materials, a group for each Product: Material, Quantity, Unit, Unit Cost, Line Cost, with the total of Line Cost.

Product margins

Every product with its price, costs and margin, for setting prices and deciding what to push.

From the query Product margins: Code, Product, Price, Material Cost, Labour Cost, Margin, Margin %.

Production plan

The open work orders for each maker in the order they are planned to start, for the Monday meeting and the workshop wall.

From the query Open work orders, a group for each Maker: Work Order, Product, Quantity, Status, Planned Start, Due Date, Days Left, with the count of Work Order.

Shortages for buying

The materials short for planned work, by supplier, for the buyer to order first.

From the query Material shortages, a group for each Supplier: Code, Material, Unit, Allocated, In Stock, On Order, Short By.

Reorder list

Materials at or below their reorder level, by supplier, with how much to order.

From the query Materials to reorder, a group for each Supplier: Code, Material, Unit, Free Stock, Reorder Level, Reorder Quantity.

Delivery schedule

Orders to deliver in the next two weeks and those late, for planning the van.

From the query Orders to deliver: Order, Customer, Kind, Order Date, Delivery Due, Days Left, Goods, with the total of Goods.

Monthly sales

Sales for each month, with a total.

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

Quotes to chase

The quotes waiting for an answer, with what they are worth, for a phone round on Friday afternoon.

From the query Quotes awaiting an answer: Quote No, Customer, Kind, Quote Date, Valid Until, Days Left, Total, with the total of Total.

Invoices due

What each customer owes, invoice by invoice, with the due dates and days overdue, for chasing payment.

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

Hours by maker

Each maker's booked hours and their cost month by month, with totals.

From the query Maker hours by month, a group for each Maker: Month, Hours, Cost, with the total of Hours, total of Cost.

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.