Frank,

Joel has an excellent suggestion for getting YTD sales data into Excel, but
if you need to use an €œapproved€� number, then keep reading.

Frank, I assume that you want to use the numbers generated in the Access
report to make certain that you are using the same number as other
departments. Please note that Excel is perfectly capable of retrieving and
consolidating this data from a number of different sources, not only from
Access, but also including databases kept in other vendors Relational
Database Management Systems (RDBMS), such as Oracle. If you have a legal or
departmental reason to use only an €˜approved number from a specific report,
then this is where we will go for now. We will attempt to gather the exact
same data as the Access report; if possible, even using the same gathering
tools.

There are a number of ways to do this. There are more complicated ways, but
the easiest way might be to use the query that brings data to the report to
bring that same data to Excel, then use Excel to summarize the data (on a
separate worksheet in the same workbook) and link that summary to the €˜Total
Sales cell. Excel can retrieve data from an Access query just as easily as
it can from an Access table.

Frank, this is going to look complicated, but as you get into it and take it
a step at a time, it will be simple, at least the second time you do it.
After finding all the pieces the first time, you will be able to do this in
your sleep.

Access reports normally get numbers and other data from various sources
using Structured Query Language (SQL) statements. (Please see Note 1.) These
statements are called Queries in Access. Access reports can use queries in
several ways. Generally, in my experience, the three most popular would be:

1. A query is made and saved in the Access database file and the report is
linked to this pre-made query when the report is developed.
2. The report is developed without an external query; the query is created
during the design process of the report. The query is then stored inside the
report design as a SQL statement and is not a stand-alone query. Both 1 and 2
are static queries, the queries do not change.
3. The report is designed for use with a dynamic query. Typically, in Access
this would be a report that is called from an Access form. The form (an
interactive document, as compared to a view-only Report) would allow the user
to make selections such as date range, sales area, and/or sales person.
Typically, the user would then click a €œshow report€� button on the form. In
the background, the code behind that button would build a SQL statement (the
query in text form) and then would call the report while linking the SQL
statement to the report at run time. This method, and number 2 above, could
present some slight, albeit different, challenges.

The report builder might have used the query to simply select just
year-to-date sales records and then used Access report tools to do the
summarization. On the other hand, a certain amount of summarization can be
done in SQL and the report could have received pre-summarized data. Two ways
to find this out: open the report in design view or ask the report builder.

Assuming you would have already asked the builder if that option was
available, you may need to go into the report and look for the necessary
information. From here we must assume that you have security permissions to
see the database window (Please see Note 2.) with all of the database objects
and are not locked behind a menu. Even if the report is locked behind a menu,
the possibility may still exist to open the report in design view.

Assuming that you can see the report in the report page or tab of the
database object window, highlight the report and click €˜Design in the
database window tools, or right-click the report and select €˜Design.

From this point on, be careful to not save any changes you may inadvertently
make in the report; just say no if asked by a dialog box if you want to save,
and stay away from the little 3.5 floppy disk €˜save emblem in the Access
window while the report is open in design view. It would be safer to work
with a copy of the report, if you can create objects in the database. (Right
click the report and Copy, click a clean area in the database report objects
window and click Save, provide a name for your temporary copy and OK. Then
use the temp copy to discover the data source or sources used. Delete the
temp copy when finished)

If you are opening the report from a menu and not directly opening it in the
database objects reports window, then a final possibility is to open the
report first and this may give you access to the Design view button. This
button has a generally triangular shaped symbol on it and is usually on the
far left of the tool bar in the window immediately above the report.

If you can get the report opened in design view, then you need to €˜show the
€˜Properties window for the report. This button has a small hand holding a
sheet of paper as the icon. If you call the Properties window as soon as you
open the report in Design view then you will have the properties of the
report showing; if you have clicked on anything in the report, then the
properties of whatever you clicked will be showing. The word €œReport€� should
show in the top of the properties window and there will be a dropdown box in
which you can select €œReport€� if needed.

What you want is the Data tab or section of the Properties window, once you
make sure you are looking at the Report Properties. This tab contains a
property called €œRecord Source€� and this is where the rubber meets the road
in getting data into the report.

What you find in €œRecord Source€� may take different forms. The name of a
table or saved query may be used there, or an actual SQL statement. If good
naming conventions have been used in the database, the name of a query will
contain clues, such as the word query or something like €˜qry used as part of
the query name. If it is based on a single table, then a word or table title,
such as €œSales_Data€� might be all that appears there. If it is a single word,
or short title-like phrase and it is not obvious that it is a table or query,
look in the Queries tab of the database objects window and see if you find
the name there.

If you find a saved query, you are practically home free, sort of. This
query might be an Aggregate query that first retrieves and filters, then
groups and compiles the data before sending it to the report, or it could be
a simple Select query that only retrieves and filters the data (in this case,
by date for the YTD report, for instance) before it sends the data to the
report.

Frank, there are several possible branches from here, too many to go forward
without further guidance into your exact situation, so if you are still
there, please let me know whether you would prefer to duplicate the reports
numbers from scratch in Excel, or would like to use the exact same query as
the Access report.

Basically, I need to know if you have and can get this far with what I have
written here, or if this needs further explanation, and if you would (and
can) just get those sales numbers directly without going to all of the
trouble of using the exact same query as the Access report. If you want to
continue this way, once we are able to select the same records as the report,
we will still be doing the aggregating and compiling in Excel. This method
will give you the exact same number as the report, it is all a matter of
making sure we select the exact same records as the report.

If you are still there at this point, please let me know how (and if) you
want to go forward, that is if you have not already solved the problem on
your own.
Hope this helps, let me know if you are interested in pursuing this. I have
the automatic notification set so just reply here.

SongBear

Note #1. Access reports can be based on a single table with no query
involved. This is unusual because, normally the data necessary to make a
coherent report is scattered in several related tables. If, in your case, a
single table contains all sales data and the report uses only that table, no
problem, we can handle that.

Note #2. The database object window can be either a free-floating window
inside of the Access main application window, or, if maximized, an extra
level of icons and object windows just inside the main Access application
window. If maximized the database object window is normally identifiable by
having a different color menu/border bar than the Access application window.
Security settings can make is inaccessible to users, so if you cannot get to
it, you may be stuck with duplicating the reports numbers directly with
Excel database queries, or finding the reports creator and gaining access
that way.


"Frank Situmorang" wrote:

In excell worksheet there is a cell named: Total Sales which requires data
from Access sales data sales which has the report/form if we run can produce
the total YTD Sales.

My question is how can we link this excell cell to the access total YTD
sales. We know in access can only produce report if we run it, while
we(Budget control Dept) want the data be avaialbe any time. Because Budget
Contorl dept needs their excell worksheet be uptodate anytime (Hotline) We
are using network for this purpose.

We appreciate your idea/help

Frank