EXCEL MS Query create derived date fields

[JPL3]

Have an application in ACCESS that I want to move to EXCEL, but I can't figure out how to do things in the EXCEL QBE grid. Need to create several alias fields from a date field, such as Year, Month Number, Day of week Number, etc. Trying to add new column using the following to create alias and the year: Year:Date.Year( but I can't enter the column name that contains the date. If I try to use the drop down menu to get the date field it just replaces everything I've entered. And I can't manually enter it. (Note that the date field is a date field, not text) I have combed the web and can't find any tutorial that really explains the EXCEL QBE Grid. Been using ACCESS for a number of years and there is information all over the place for that. I can connect to the proper database, do a join and get the columns I need, I just can't create the 7 or so date fields I need. The data will be loaded into a Pivot Table on open.

Thanks

jpl3

 

[John_w]

I've never seen the QBE UI in Excel; does it have one?

You can edit the SQL command text to insert the required alias fields. For example, to derive the Year and Month number from a Date field:

SELECT `Events$`.`Start Date`, Year(`Events$`.`Start Date`) AS 'Year', Month(`Events$`.`Start Date`) AS 'MonthNum' FROM `Events$` `Events$`

In Excel this is Data tab -> Connections -> Query from Excel files -> Properties -> Definition tab -> Command text.

 

[JPL3]

I've never seen the QBE UI in Excel; does it have one?

You can edit the SQL command text to insert the required alias fields. For example, to derive the Year and Month number from a Date field:

SELECT `Events$`.`Start Date`, Year(`Events$`.`Start Date`) AS 'Year', Month(`Events$`.`Start Date`) AS 'MonthNum' FROM `Events$` `Events$`

In Excel this is Data tab -> Connections -> Query from Excel files -> Properties -> Definition tab -> Command text.

Using EXCEL 365 and don't have > connections-> so I am stopped there. have Queries and connections and it opens a blank window. Also, your example looks a lot like the SQL from ACCESS. Can I just copy that into someplace I don't know how to get to? Thanks for the response, it was helpful.

 

[John_w]

The SQL is from Excel 2016. You should be able to edit the SQL command text in the same place as Excel 2016 in Excel 365 - see https://support.office.com/en-us/article/connection-properties-9d3599a9-e9b3-461d-99b2-c5505ddae6e0