|
Extract
Accpac data and manipulate it in Microsoft Excel:
The
following example shows how you can extract Accpac data in
Microsoft Excel using Database query.
As an example we will extract customer sales for the month
of July with the purpose of budgeting December sales as a 20%
increase on July sales.
 |
Open Excel and
under the Data menu select “Import External Data” and
“New Database Query” |
 |
From
the list of databases select your Accpac Database ID .
Please note the existing checkmark at the bottom of
the Window. This will enable you to take advantage of the
simplified steps in the Wizard.
Click OK to go on to the next step in the
Wizard. |
 |
Now
you have to select the tables and the fields necessary to
build the query. In
our example we will use two tables : ARCUS – Customer
master file and ARCSM – Customer Statistics.
The
fields to select are as follows:
-
NAMECUST
(customer name)
-
CNTYR
(fiscal year)
-
CNTPERD
(fiscal period)
-
AMTINVCHC
(Total invoices in functional currency)
-
AMTCRHC
(Total credits in functional currency)
-
AMTDRHC
(Total debits in functional currency)
|
If
you select one of the fields and click Preview Now you will be
able to look at the information contained in the fields and makes
it easier to decide which fields you should use.
 |
In the next
step you can put conditions to filter the info you want to
extract: in our example we will extract the records for year
2007 fiscal period 07; select the field you want and in the
drop-down boxes to the right build your criteria: |
 |
You can also sort
the records, for example from the highest amount to the
smallest. |
 |
In the last step
the Wizard also gives you the possibility of saving the
query for later use (click Save Query and give the query a
name).
Click Finish to complete the
Wizard. |
|
NAMECUST
|
CNTYR
|
CNTPERD
|
AMTINVCHC
|
AMTCRHC
|
AMTDRHC
|
Total
|
Budget
December
|
|
|
Dr.
Dan Penn
|
2010
|
7
|
753.25
|
0
|
0
|
753.25
|
903.9
|
|
|
Mr.
Ronald Black
|
2010
|
7
|
565.4
|
0
|
0
|
565.4
|
678.48
|
|
To
manipulate the info create a new column to total your invoices ,
debits and credit and an additional column to add a new budget
column showing a forecasted 20 percent increase in sales for
December compared to July.
Similar to the example
above you can extract Accpac info in Microsoft Word and Microsoft
Access, manipulate and report on the data without affecting your
“live” Accpac data.
Please contact us if you
have a specific scenario that you might need assistance with.
|
