Finding month and dates and values

  • Thread starter Thread starter Kim Jensen
  • Start date Start date
K

Kim Jensen

Hi everyone,

I have a time series containing: A date for each day and a number for each
day

Date; Number;

Jan-28-04; 100
Jan-29-04;110
Jan-30-04;120
Jan-31-04;130
Feb-01-04;110
Feb-01-04;105

How do I make an query that extracts only month end dates and values. There
might be months where I only have data until for instance the 27th ?
Anybody who has a solution?

Best regards,
Kim
 
Kim,

The "simple" way to do this is by using two queries: one that will return
the last date in each month existing in your table, and a second one that
will join the first one with the original table to return the corresponding
number.
First query:
In design view add the table and get the Date field down to the grid. Add
another two calculated fields, by typing the following expressions in the
field line of the grid in the next two available columns:
D_Year: Year([DateFieldName])
D_Month: Month([DateFieldName])
(change DateFieldName to the actual name of your date field)
Set both calculatef fields' sorting order to ascending.
Click on the Totals sign (Greek Sigma) on the toolbar, and select totals
function Max under the date field, leaving the other two to default Group
By. Your query should now return the last date in each month (check in
datasheet view).
Save the query.
Open a new query in design view, and add the previous one and the original
table.
Join on the date fields, and get both fields of the original table down to
the grid. Your query should now return the desired output. Check and save.

Alternatively, you could do it with one query like the first one above,
adding a calculated field with a DLookup function on the original table to
return the number for the selected date (which would, I guess, require a
DMax function nested in the criteria part of the DLookup one), but this
might prove significantly slower to execute, especially as your table gets
larger, so I wouldn't recommend it.

HTH,
Nikos
 
Back
Top