Turn quotes into paid invoices by using the Services Web Database template
The Access 2010 Services web database template creates a ready-to-use database that lets you track quotes, invoices, customers, and other data associated with running a service business.
The following video shows a brief scenario of how this template might be used.
If you haven't already, download the Services template.
Read on to learn how to use the template to make your own database and start using it to help run your business.
Note: This article doesn't apply to Access web apps – the newer kind of database you design with Access 2013 (or higher) and publish online. See Create an Access web app for more information.
In this article
Start the database for the first time
A dialog box appears which allows you to choose whether to keep the sample data or delete it so that you can begin entering your own data:
Click the button on the left to open the database and work with the sample data. Access will populate the Services database with data from the fictional company Northwind Traders.
Click the button on the right to delete the sample data so you can begin entering your own.
Note: Deleting the sample data also prevents the startup dialog box from appearing again later.
Clear the check box to prevent the dialog box from appearing again later.
Note: All fields and controls in the Services template that display currency data use a dollar sign ($) as the currency symbol. If your locale uses a different currency symbol, you should consider adjusting the affected fields and controls before you remove the sample data. For more information, see the See Also section.
Getting Started: Add existing data to your Services database
The Getting Started tab includes the following other tabs to help you get your business data in place and ready to go:
| Getting Started tabs | Features |
| Links to helpful resources | |
| Fields to store information about your company for use on invoices and other reports | |
| Datasheet view of employees. The logged-in employee is recorded on new quotes and invoices | |
| Datasheet view of the services you can add to quotes and invoices | |
| Datasheet view of materials you resell or use. You can add these products to quotes and invoices | |
| Two datasheet views: your quotes, and your invoices |
When you click Start entering my data on the startup dialog box, Access takes you to the Getting StartedWelcome tab, where you can begin the process of entering your data into your new Services database.
You don't have to use the Getting Started tab to enter all your data. The tab is there for your convenience in getting started quickly. Most of the information that you can enter on the following Getting Started tabs can also be entered on the main tabs, such as the Quotes tab or the Invoices tab. However, you can only enter your company information (which will appear on quotes and invoices) by using the Company Info tab.
Welcome tab: Help and other resources
The links on the Welcome tab point to the most current resources to help you make the most of your Services database. Although the links do not change, the link targets are updated as new resources become available.
-
When you are finished viewing the information on the Welcome tab, click Next to proceed to the Company Info tab.
Company Info tab: Enter your company information
On the Company Info tab, you can enter information about your company, such as name, location, phone, and email address. This information will be displayed on invoices and other reports created by the database.
-
Type in your company's information into the boxes, and then click Next.
Employees tab: Enter employee information
On the Employees tab, you can begin adding employees to the database. This will help you keep track of which employee did what. For example, when a new quote is created, the employee who is logged in is recorded as part of the new quote.
-
Type each employee's information into the datasheet, and then click Next.
You can also view, edit, and add employees on the main Employees tab.
Services tab: Enter service information
On the Services tab, add information about the services you provide, such as the hourly rate for each. You can also remove services by deleting them or marking them discontinued. You can only delete a service if it does not appear in any quotes or invoices.
Note: When you add a service to a quote or invoice, the default hourly rate for that service is added to the quote or invoice. However, you can adjust the hourly rate for any service before you submit any particular quote or invoice. Adjusting the rate on a quote or invoice will not affect the default hourly rate for the service.
Add services data that you have stored in Excel
You can copy and paste data from Excel into an Access datasheet. Copying and pasting that data can save you time, especially if the data in Excel is already in rows or columns that are the same or very similar to the columns on the datasheet.
There are two main considerations when you prepare to copy Excel data and paste it into Access:
-
If the Excel data is arranged in rows (as opposed to columns), you should transpose the data in Excel before you paste it into Access.
-
If the Excel columns are not in the same order as the columns on the Access datasheet, you should rearrange the columns in Excel before you paste them into Access
Important: The column Service Code must have a value for every service, and the value must be unique – i.e., two or more services can't have the same name.
Note: The steps in this section assume that you have data for all four columns in the Services datasheet. If you don't have data for all them, you can copy the data that you do have. The only column that is required and must have unique values is Service Code.
Transpose Excel data from rows into columns
Tip: If your data is not ordered the same as the columns in the Access datasheet, consider inserting a worksheet in Excel and transposing the data onto the new worksheet. Using a new worksheet can make it easier to arrange the data in the same order as the Access datasheet columns.
-
In Excel, select the data, and then press CTRL+C.
To select several non-adjacent groups of cells, press and hold the CTRL key while you select.
-
Right-click a cell that is outside your selected data (for example, a cell below the lowest row of your selection), and under Paste Options, click the Transpose button:
Rearrange columns of Excel data that are in a different order from the Services datasheet
-
Open the Excel workbook that contains the data about your services.
-
Insert a new worksheet. For help with this step, see the Excel article Insert or delete a worksheet.
-
On the new worksheet:
-
Type or paste Service Code in cell A1.
-
Type or paste Service Description in cell B1.
-
Type or paste Default Hourly Rate in cell C1.
-
Type or paste Notes in cell D1.
-
-
Copy the names or codes of your services into column A:
-
Click the tab of the worksheet where the names or codes are listed.
-
Select the list of names or codes and then press CTRL+C
-
Click the tab of the new worksheet.
-
Select cell A2, and then press CTRL+V.
-
-
Copy the descriptions of your services into column B:
-
Click cell B2 and then type an equal sign (=).
-
Click the tab of the worksheet where the descriptions are listed.
-
Click the cell that contains the first description, and then press ENTER.
Excel pastes the cell reference into cell B2 on the new worksheet. -
On the new worksheet, click cell B2 again and then double-click the fill handle
.
Excel pastes the cell references for the rest of the list into column B.
-
-
Repeat step 5 but use column C and copy the default hourly rates for your services.
-
Repeat step 5 but use column D and copy the notes for your services.
Paste data from columns that are in the same order as the columns on the Services datasheet
-
In Excel, select the data, and then press CTRL+C.
To select multiple non-adjacent groups of cells, press and hold the CTRL key while you select.
-
In Access, on the Services tab, right-click the asterisk on the datasheet, and then click Paste.
Add services one at a time
-
Click Add a New Service to open the Service Details form and enter information about a service.
- or – -
Enter data into the datasheet on the Services tab.
Products tab: Enter products information
On the Products tab, add information about products you resell or use during service.
Note: When you add a product to a quote or invoice, the unit price is recorded as part of the quote or invoice. However, you can adjust the unit price for any product before you submit any particular quote or invoice. Adjusting the price on a quote or invoice will not affect the unit price for the product.
Add product data that you have stored in Excel
You can copy and paste data from Excel into an Access datasheet. Copying and pasting that data can save you time, especially if the data in Excel is already in rows or columns that are the same or very similar to the columns on the datasheet.
There are two main considerations when you prepare to copy Excel data and paste it into Access:
-
If the Excel data is arranged in rows (as opposed to columns), you should transpose the data in Excel before you paste it into Access.
-
If the Excel columns are not in the same order as the columns on the Access datasheet, you should rearrange the columns in Excel before you paste them into Access
Important: The column Product Code must have a value for every service, and the value must be unique – i.e., two or more services can't have the same name.
Note: The steps in this section assume that you have data for all four columns in the Products datasheet. If you don't have data for all them, you can copy the data that you do have. The only column that is required and must have unique values is Product Code.
Transpose Excel data from rows into columns
If your data is in rows (as opposed to columns), transpose the data in Excel before you paste it into Access.
Tip: If your data is not ordered the same as the columns in the Access datasheet, consider inserting a worksheet in Excel and transposing the data onto the new worksheet. Using a new worksheet can make it easier to arrange the data in the same order as the Access datasheet columns.
-
In Excel, select the data, and then press CTRL+C.
To select several non-adjacent groups of cells, press and hold the CTRL key while you select.
-
Right-click a cell that is outside your selected data (for example, a cell below the lowest row of your selection), and under Paste Options, click the Transpose button:
Rearrange Excel columns that are in a different order from the Products datasheet
-
Open the Excel workbook that contains the data about your products.
-
Insert a new worksheet. For help with this step, see the Excel article Insert or delete a worksheet.
-
On the new worksheet:
-
Type or paste Product Code in cell A1.
-
Type or paste Product Description in cell B1.
-
Type or paste Unit Price in cell C1.
-
Type or paste Quantity Per Unit in cell D1.
-
Type or paste Notes in cell E1.
-
-
Copy the names or codes for your products into column A:
-
Click the tab of the worksheet where the names or codes are listed.
-
Select the list of names or codes and then press CTRL+C
-
Click the tab of the new worksheet.
-
Select cell A2, and then press CTRL+V.
-
-
Copy the descriptions of your products into column B:
-
Click cell B2 and then type an equal sign (=).
-
Click the tab of the worksheet where the descriptions are listed.
-
Click the cell that contains the first description, and then press ENTER.
Excel pastes the cell reference into cell B2 on the new worksheet. -
On the new worksheet, click cell B2 again and then double-click the fill handle
.
Excel pastes the cell references for the rest of the list into column B.
-
-
Repeat step 5 but use column C and copy the unit price for your products.
-
Repeat step 5 but use column D and copy the quantity per unit for your products.
-
Repeat step 5 but use column E and copy the notes for your products.
Paste data from columns that are in the same order as the columns on the Products datasheet
-
In Excel, select the data, and then press CTRL+C.
To select multiple non-adjacent groups of cells, press and hold the CTRL key while you select.
-
In Access, on the Products tab, right-click the asterisk on the datasheet, and then click Paste.
Add products one at a time
-
Click Add New Product to open the Product Detail form and enter the product information.
- or – -
Enter data into the datasheet on the Products tab.
Quotes & Invoices tab: Enter quotes and invoices
If you already have some quotes and invoices on hand, you can enter them into the datasheets on the Quotes & Invoices tab.
-
To enter a quote, click Add New Quote. To enter an invoice, click Add New Invoice.
-
In the details form that opens, enter the information you have, such as the status, the employee who produced the quote or invoice, and the customer.
-
If you enter a customer that isn't in the database, Access prompts you to enter the new customer. Click Yes, and then fill in the information you have about the new customer. When you close the datasheet, the customer will be available in the Customer drop-down list.
-
Click Save & Close after you have finished each quote or invoice. The data is saved and the form closes.
-
When you are finished adding existing quotes and invoices, click Next.
Dashboard: Track active quotes and invoices
The Dashboard tab provides a convenient place to track open quotes and unpaid invoices, and to add a new quote or invoice.
View details about a quote or invoice
-
Click a value under Quote# to open the Quote Details form for that quote.
You can edit the details of a quote before it is submitted – i.e., if the Status is New.
-
Click a value under Invoice# to open the Invoice Details form for that invoice
Create a new quote or invoice
-
Click New Quote or New Invoice, and then fill in the form that appears.
If you are not logged in to the database, select your name from the Salesperson drop-down list to add your name to the quote or invoice.
Quotes: Manage all your quotes
The Quotes tab provides views to help you manage quotes based on their status.
Quotes are listed on the following tabs:
-
Active Lists quotes that have not been invoiced, rejected, or expired.
-
Approved Lists quotes that have been approved or invoiced.
-
Invoiced Lists quotes that have been invoiced. You can view the invoice of a listed quote.
-
Expired Lists quotes which expired without being invoiced.
-
Rejected Lists quotes which have been rejected. You can approve a rejected quote to make it available for invoicing.
View details about a quote
-
Click a value under Quote# to open the Quote Details form for that quote.
You can edit the details of a quote before it is submitted – i.e., if the Status is New.
Quote Details form
The quote number and status appear in the upper left corner of the Quote Details form.
Use the buttons at the top of the Quote Details form to submit, approve, reject, or invoice a quote, to create a duplicate of a quote, and to view a quote in preparation for printing.
General information about a quote goes in the boxes just below the buttons at the top of the Quote Details form.
The tabs in the middle of the Quote Details form are for details about the quote: line items (services and products), service location, taxes and terms, and notes. You can adjust the rates and unit prices for line items on any quote without affecting the default value for the associated service or product.
Total amounts for services, products, and tax are displayed at the bottom right corner of the Quote Details form. If you update details on the Line Items tab or the Taxes & Terms tab, click Refresh Totals to update the amounts shown in this area.
Create a new quote
-
On the Quotes tab, click New Quote, and then fill in the Quote Details form.
If you are not logged in to the database, select your name from the Salesperson drop-down list to add your name to the quote.
Submit a quote for approval
Quotes that haven't been submitted do not have a quote number; the value […] is displayed under Quote#.
-
On the Quotes tab, click […] in the row containing the quote that you want to submit.
Access opens the Quote Details form and displays the quote. -
Review the general information and details for the quote and make any necessary changes, such as adding or removing line items, adjusting the rate or unit price for a line item, or adding notes.
-
At the top of the Quote Details form, click Submit.
Approve or reject a submitted quote
-
On the Quotes tab, click a value under Quote# to open the Quote Details form and display the quote.
-
Review the quote information and do one of the following:
-
If the quote is acceptable, click Approve at the top of the Quote Details form.
-
If the quote would be acceptable with minor changes, click Duplicate at the top of the Quote Details form. Access creates a new quote based on the original. You can then revise and submit the new quote, and then approve it.
-
If the quote is unacceptable, click Reject at the top of the Quote Details form.
-
Turn an approved quote into an invoice
-
On the Quotes tab, click the Approved tab.
Access displays quotes that have been approved, including quotes that have already been invoiced. -
Click a value under Quote# to open the Quote Details form for that quote.
-
Review the quote information.
Note: You can't changes any details of a quote that has been approved. However, you can make a duplicate quote by clicking Duplicate, revise and submit the duplicate quote, and then approve and use that quote to create an invoice.
-
At the top of the Quote Details form, click Invoice.
Print or email a quote
-
On the Quotes tab, click a value under Quote# to open the Quote Details form and display the quote.
-
At the top of the Quote Details form, click View Quote.
Access opens the quote in the form in which it will be printed.The buttons at the top of the quote do not appear on the final copy.
-
Click the appropriate button at the top of the quote.
Invoices: Manage all your invoices
On the Invoices tab, you can create new invoices and review invoices by status.
Invoices are listed on the following tabs:
-
Unpaid Lists invoices that have not been paid but are not yet past due.
-
Past Due Lists invoices that have not been paid and are past due.
-
Paid Lists invoices that have been paid.
View details about an invoice
-
Click a value under Invoice# to open the Invoice Details form for that invoice.
You can edit the details of an invoice before it is submitted – i.e., if the Status is New.
Invoice Details form
The invoice number and status appear in the upper left corner of the Invoice Details form.
Use the buttons at the top of the Invoice Details form to submit an invoice or mark it paid, to create a duplicate of an invoice, to view what an invoice will look like when printed, and to save changes to an invoice and close the Invoice Details form.
General information about an invoice goes in the boxes just below the buttons at the top of the Invoice Details form. If the invoice was created from a quote it inherits this information from that quote, but you can make changes to the invoice without affecting the originating quote.
The tabs in the middle of the Invoice Details form are for details about the invoice: line items (services and products), service location, taxes and terms, and notes. If the invoice was created from a quote it inherits these details from that quote, but you can make changes to the invoice details without affecting the originating quote. You can also adjust the rates and unit prices for line items on any invoice without affecting the default value for the associated service or product.
Total amounts for services, products, and tax are displayed at the bottom right corner of the Invoice Details form. If you update details on the Line Items tab or the Taxes & Terms tab, click Refresh Totals to update the amounts shown in this area.
Create a new invoice
-
Click New Invoice, and then fill in the Invoice Details form.
If you are not logged in to the database, select your name from the Salesperson drop-down list to add your name to the quote.
Submit an invoice for payment
Invoices that haven't been submitted do not have an invoice number; the value […] is displayed under Invoice#.
-
On the Invoices tab, click […] in the row containing the invoice that you want to submit for payment.
Access opens the Invoice Details form and displays the invoice. -
Review the general information and details for the invoice and make any necessary changes, such as adding or removing line items, adjusting the rate or unit price for a line item, or adding notes.
-
At the top of the Invoice Details form, click Submit.
Print or email an invoice
-
On the Invoices tab, click a value under Invoice# to open the Invoice Details form and display the invoice.
-
At the top of the Invoice Details form, click View Invoice.
Access opens the invoice in the form in which it will be printed.The buttons at the top of the invoice do not appear on the final copy.
-
Click the appropriate button at the top of the invoice.
Mark an invoice paid
-
On the Invoices tab, on the Unpaid tab, click a value under Invoice# to open the Invoice Details form and display the invoice.
-
At the top of the Invoice Details form, click Mark Paid.
Report Center: View and print reports
The Services database template includes four reports: a customer list, and three sales reports (monthly, quarterly, and yearly).
To preview a report:
-
Click the Report Center tab, and then under Select a Report, click the report that you want to view.
Access displays the report in the preview pane.
You can customize your preview by using the options that appear at the top of the preview pane. The options that appear depend on which report you preview.
To print a report:
-
Preview the report that you want to print.
-
On the Report Center tab, click Open in New Tab, and then press CTRL+P.
Advanced: Manage services, products, employees, and customers
The Advanced tab provides a convenient place to manage the following lists:
-
Customers
-
Employees
-
Products
-
Services
Each list is displayed as a datasheet, where it is easier to spot inconsistencies and make bulk changes to data.
Next Steps
As you begin to use the Services database to manage your business, you will most likely think of new ways in which you want to query or filter your data. You may also discover that you want to modify the database to better suit your needs. The links in this section take you to more information about the typical tasks and modifications that you can do.
-
For information about finding, filtering, or querying data in Access, see the following:
-
For more detailed information about creating new database objects, see the following:
-
And finally, an important article about safeguarding your data: Protect your data with backup and restore processes
No comments:
Post a Comment