Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
Hi John,
a good method to retreive data from access is described he http://support.microsoft.com/default...b;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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
"John" wrote ...
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. 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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
Jamie, MS Query within Excel whereby the database is Access
Thanks "Jamie Collins" wrote in message om... "John" wrote ... 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. 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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
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(Arra y( _ "ODBC;DBQ=E:\History.mdb;DefaultDir=E:\;Driver={Mi crosoft 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 "Jamie Collins" wrote in message om... 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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
John wrote ...
This is the Macro I've recorded to do exactly what I manually do to extract the information. 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. 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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do an Access Database Query in Excel
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 "Jamie Collins" wrote in message om... John wrote ... This is the Macro I've recorded to do exactly what I manually do to extract the information. 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. 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query to access database | Excel Discussion (Misc queries) | |||
Database query to import from Access to Excel | Excel Discussion (Misc queries) | |||
connect to access database and run a query in excel VBA | Excel Discussion (Misc queries) | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |