How to do an Access Database Query in Excel

J

John

I'm trying to Run an database query from Excel using an Access database. I
can get the values returned in to Excel, that's not a problem. The results
details sales by Location, by product by date. My problem is that the data
is to big to fit within Excel (an its only one months worth of data).

As I wish to use Excel, how can I edit the query within Excel so that the
data that is returned is by Location, by Product, within the overall dates
that I originally selected i.e. I don't need to know that I sold 4 Widgets
on the 1st, 10 on the 2nd etc, only that Location A sold 55 within the range
I selected.

I was told before that there is a download needed from Microsoft

Thanks
 
A

arno

Hi John,

a good method to retreive data from access is described here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;246335

In the code you will find a sql-query that you should adapt to your needs.
Instead of "select * from mytable" you can specify the fields you want to
see (less than 256) and you can specify conditions/group by to reduce the
number of records you get (less than ca. 65536).

arno
 
J

Jamie Collins

John said:
I'm trying to Run an database query from Excel using an Access database. I
can get the values returned in to Excel, that's not a problem. The results
details sales by Location, by product by date. My problem is that the data
is to big to fit within Excel (an its only one months worth of data).

As I wish to use Excel, how can I edit the query within Excel so that the
data that is returned is by Location, by Product, within the overall dates
that I originally selected i.e. I don't need to know that I sold 4 Widgets
on the 1st, 10 on the 2nd etc, only that Location A sold 55 within the range
I selected.
Click to expand...

What do you mean by 'Access Database Query'? Do you mean you are
calling a Query object you created in the MS Access UI? Or are you
executing SQL against a Jet database? Are you using MS Query, ADO or
something else?

Jamie.

--
 
J

Jamie Collins

John,
If your existing query looks like this:

SELECT
location, product,
sale_date, sale_quantity
FROM
Sales
;

then you want something more like this:

SELECT
location, product,
SUM(sale_quantity) AS sale_quantity
FROM
Sales
WHERE
sale_date BETWEEN
#01 MAY 2004# AND #30 MAY 2004#
GROUP BY
location, product
;

For fuller details, post your raw data, what you want your processed
data to look like and your existing SQL text.

Jamie.

--
 
J

John

Jamie

This is the Macro I've recorded to do exactly what I manually do to extract
the information. Before it exports to Excel I wish it to SUM by Location
each Item sales, so not sure where I would put that in the code below. I
want to sum in order to extract more than 1 months data. Currently 1 month
is approx 59,000 line!

Thanks


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=E:\History.mdb;DefaultDir=E:\;Driver={Microsoft Access
Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _
), Array( _

"nRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")),
_
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblSalesMixHistory.StoreNo, tblSalesMixHistory.SalesDate,
tblSalesMixHistory.Item, tblSalesMixHistory.MenuLookupName,
tblSalesMixHistory.Sold" & Chr(13) & "" & Chr(10) & "FROM
`E:\History`.tblSalesMixHistory tblSalesMixHi" _
, _
"story" & Chr(13) & "" & Chr(10) & "WHERE
(tblSalesMixHistory.SalesDate>={ts '2004-05-31 00:00:00'} And
tblSalesMixHistory.SalesDate<={ts '2004-06-27 00:00:00'})" & Chr(13) & "" &
Chr(10) & "ORDER BY tblSalesMixHistory.StoreNo" _
)
.Name = "Query from HOBO - History Sales Mix"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
 
J

Jamie Collins

John wrote ...
This is the Macro I've recorded to do exactly what
I manually do to extract the information.
Click to expand...

Just like the macro recorder writes awful code, the MS Query wizard
writes lousy SQL. Both should be give a sanity check before going into
production.
Before it exports to Excel I wish it to SUM by Location
each Item sales, so not sure where I would put that in
the code below.
Click to expand...

I thought I answered this in my last post. You may want to use the
GROUP BY syntax I suggested. You'd replace the existing SELECT query
with my amended syntax. I'm not sure what your columns are (have they
changed since your original post?) because you haven't given any
schema information and I haven't tested because you didn't supply any
sample data. As a guess:

.CommandText = "SELECT StoreNo, Item, SUM(Sold) AS Sold" & _
" FROM tblSalesMixHi WHERE SalesDate Between" & _
" #31 MAY 2004# And #27 JUN 2004# GROUP BY StoreNo," & _
" Item ORDER BY StoreNo"

Of course, to do this 'properly', you'd create a procedure on the
server (database) and pass it the date parameters e.g. your command
text should be as simple as:

.CommandText = "EXEC MyStoredProc '31 MAY 2004'', '27 JUN 2004'

To achieve this, run something like this on the database server e.g.
in a new blank Query object in MS Access:

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
StoreNo, Item, SUM(Sold) AS Sold
FROM
tblSalesMixHi
WHERE
SalesDate
BETWEEN start_date AND end_date
GROUP BY
StoreNo, Item
ORDER BY
StoreNo
;

Jamie.

--
 
J

John

Thanks once again Jamie, not much knowledge of Access and I can find my way
around (recorded) Excel code.

Your suggestions is that I create a procedure within the Access database and
simple call this procedure from within an Excel macro

The columns of Data I import from Access are -
StoreNo, SalesDate, Item, MenuLookupName, Sold and these are all taken from
the following location / table "E:\History`.tblSalesMixHistory
tblSalesMixHistory"

I can't test for a couple of days, but will let you know

Thanks
 
Top