ms query

Can someone please help me with this sql statement for Excel MS Query? I tried my best and couldn't get it to work. All I want is two columns - Sum of DTOTAL grouped by HENTDT (order date). I prefer to have date range parameters picked up from two cells in Excel, but not necessarily; I could...

In Excel 2013, If I record a macro for an MS Query to an Access database, the corresponding VBA code appears to be redundant, showing the query syntax twice. ONe has a .CommandType = xlCmdSql, the other has a .CommandType = 0. In any event, if you try to run the macro, it doesn't work. If you...

Hello, I am using MS Query to extract data from a QuickBooks company database. The query is complex enough that it "can't be presented graphically." Therefore, I cannot add parameters to it, at least not initially. After I get the data into Excel, I can then edit the Command Text within the...

Hi, I receive the below error when trying to edit a MS Query table by right clicking Table>Edit Query ODBC Excel Driver Login Failed The Microsoft Access database engine cannot open or write to the file ‘(unknown)’.It is already opened exclusively by another user, or you need permission to...

Hi im using Microsoft Query to pull data from a table on 1 sheet to another sheet in the same workbook. My problem is some of the columns I cannot sum looks like they are text (left aligned). I have about 20 columns and 7 columns will not sum (on table sheet1 some are straight number inputs...

I have an spreadsheet which has Excel tables with team data on. On one page I'm using an MS Query with SQL to query these tables. Here's the original thread (If you really want to know)...

Hi - first post here in a long time, and could not find a related topic thru search. The problem I am trying to solve is turning off all filters on a table of data returned by MS Query (many columns, one or more of which may have a filter). However, ActiveWorksheet.AutoFilterMode and Active...

I have a master log that I import using Microsoft Query to 4 other spreadsheets (called Regional Logs), depending on the contents of the column "Region." I have all this figured out, but I can't figure out how to have the highlighting persist through the export and refresh. I edit the Master...

Morning everyone I currently have a sheet which calculates our customers' spend sorted by customer then by rep then week. This bit is working fine, I use an MS Query to fetch in all the jobs we have done so far this year. This list is getting a bit large already (30000+ rows) and I can...

I have data in an Excel 'table' which was returned from MS QUERY. (I'm using the word 'Table' formally here). One field has entries that look like dates as in '2012-04-01' but the field is a text field and not a date field, and I need to change it to a date field to make it useful to me. The way...

Hi, I'd like to do a FULL OUTER JOIN of 3 tables in Excel. Each table has 4 columns - 3 categories (for joining) and one metric column. All the 3 joining columns are the same across the 3 tables. As MS Query does not support the FULL OUTER JOIN command it can only do this using UNION. There...

Thanks for reading and trying to help! I have several MS Queries in an Excel Workbook that are called from within a VBA macro. In one or two (varies) the query will return all the data I need but it will not have the database field names in the query result columns (it will show Column1...

I'm creating a spreadsheet to be used for calling in employees for after hours and weekend work. I have a master spreadsheet which lists all employees, along with their work center, job skill & contact information. The call-in log is located in another workbook. There is a worksheet for each...

Hello, I regularly find myself manually changing the SQL in many different worksheets owing to a minor change in the criteria. For each sheet, the SQL is the same other than for the filtered field, Specialty. So: one sheet for Cardiology, one for Ophthalmology etc. I think this must be...

If I am pulling data from an external, Access table, Excel uses MS Query and it does not appear I can include multiple WHERE items in my sql statement. Even if I edit the properties and change it to say this: EnzymesMo.YYYYPP>='201102' AND EnzymesMo.Project ID='2121' It gives me an error...

Hi - I have an Excel file that is using MS Query to pull data from Access. I am wanting to store the username's initials in a table in MS Access each time the user queries the database via MS Query. Is this possible?

Hello, I have an xls file (around 10 tabs) with pivot tables that use an MS Access table as data base. Problem is: I have to frequently change the link to the Access DB as the DB is monthly put onto a different location (reporting purpose). Now, I am aware about how to change the link to the...

I have been trying to extract the same data set via an ODBC connection to a main frame database. One attempt has been using Access simply Query and the other via MS Query. The Access simple query does not return the information I need yet MS Query does. How can there be a difference if I am...

Hi I am using MS Query to return data into Excel. I know Access but am quite new to MS Query and keep finding vast differences between the two!! I've looked on the help but it doesn't seem to be of any! I have two date fields which are formatted as dd/mm/yyyy hh:mm - and I need to take one...

Hi I am using MS Query to return data into Excel. I know Access but am quite new to MS Query and keep finding vast differences between the two!! I've looked on the help but it doesn't seem to be of any! I have two date fields which are formatted as dd/mm/yyyy hh:mm - and I need to take one...