Change MS Access query from Excel

[Andrew]

Hi,

Can someone please put me out of my misery. I've been working on this for
hours.

In an Access database called 'TEST.mdb' I have a query called 'qry_My_Test'.
I want to change the 'criteria'

From

SELECT Tbl_Comm_Data.DEPT_NO FROM Tbl_Comm_Data;

To

SELECT Tbl_Comm_Data.DEPT_NO, Tbl_Comm_Data.DEPT_NAME FROM Tbl_Comm_Data;

Thanks

 

[JP]

Can't you just open Access and fix the query?

Otherwise you might need to recreate the entire query, and update the
database with it. Which means you would need access to the query to
know what's in it. Which means you should be able to just update the
query directly.

--JP

 

[dmoney]

are u trying to make the access query save or just trying to select more
information from the access table? Your select statement should work, but
how are u putting the information into excel? or are you. --- If you are
trying to litterally change the access querry, you cannot use a select
statement, u will need to use a create routine instead. The select statement
is used to querry the database not change it. If the dbase that you are
querrying has the fields you need then the select statement you wrote will
work fine followed by a open and recordset commands to put the data in Excel.

 

[Andrew]

Thanks dmoney.

I thought I'd keep my question simple but I can it just created confusion
judging by the two replies I've received.

My real requirement is to update a pivot table in excel. The source data
currently comes from MS Query and has been looking at a table based on the
Dept_No.
The SQL is:
"SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Dept_No INNER JOIN tbl_Comm_Data ON
Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO;"

There are some occaisions when the query needs to be based on the following.

"SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Report_Periods INNER JOIN (Tbl_Dept_No INNER
JOIN tbl_Comm_Data ON Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO) ON
Tbl_Report_Periods.REPORT_PERIOD = tbl_Comm_Data.REPORT_PERIOD;"

I have raised a question on how to modify the SQL in MS Query but no-one has
replied. I then thought if I could change a query (qry_Excel_Data) in access
from excel I could have the pivot tables look in Access for this query and
bypass MS Query altogether.

Hope you can assist.