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.

--