Skip to content
Advertisement

Query end of week and end of month data

In an Access database, if I have some stock data that is in the format of

and note that it goes from 05 to 08, as well as DateFormatted being a Short Text, rather than a Date. It seems like it will be almost impossible to query end of month (01-31) based upon a specific date in a WHERE clause, given the continuous date format. Instead, I am hoping that since such values may not exist in the database (as caused from weekends, public holidays, system errors etc), which will mean end of week may end on a Thursday, start of week may start on a Tuesday, end of month may be 3 days before the final day of the month, etc, there is a known method of obtaining such data in a query. How would this be possible?

Noting that in the above example, end of week will be 1.05 and end of month (if you keep continuing the trend of +0.01 for each day), will be 1.31.

Advertisement

Answer

If you want the last row in the data for each month, you can use a correlated subquery:

You can do something similar with datepart() for weeks.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement