Need help with a simple query

  • Thread starter Thread starter S Davis
  • Start date Start date
S

S Davis

Hello, I need help with this problem. I have two lists of dates. I am
using a table as a filter for this list. What I want to do is create a
final listing of all dates that are less than the minimum date in the
filter. This is probably easier to demonstrate than to explain:

Data:
1/3/2007
1/2/2007
1/1/2007
12/31/2006
12/30/2006
12/29/2006

Filter(Table)
2010
2009
2008
2007

Essentially, I want to display the MAX value from Data that's year is
less than the minimum value of Filter(Table). The result should be
12/31/2006

Thanks!
 
SELECT
Max(data_field) AS max_date
FROM
data
WHERE
Year([data_field]) Not In (select filter_field from filter);

Cheers,
Jason Lepack
 
Alright, that worked for the most part. The part I left out was that I
am using this list of dates to find the last known location of
something. So when pulling a max, it shows the maximum date for each
location, not just the maximum date which is what I am after. Example:

Data:
1/3/2007 Location1
1/2/2007 Location2
1/1/2007 Location3
12/31/2006 Location4
12/30/2006 Location4
12/29/2006 Location5


Filter(Table)
2010
2009
2008
2007

would show me
12/31/2006 Location4
12/29/2006 Location5

and so on

Any tips or ideas, please post up. Many thanks to everyone.
 
Don't ask for one thing and then expect the answer to be the solution
to another. How would you like it if I told you that I wanted a
chocolate bar, and then when you brought me a kitkat, I said, "No, I
wanted a Snickers." Wouldn't you be frustrated?

So, next time, state what you want, not part of what you want...

Anyways,

save that query that you have.
Create a new query, add that query that you created and the data
table. Join the two on the date field. Add the fields you want.

Cheers,
Jason Lepack
 
SELECT
Max(data_field) AS max_date
FROM
data
WHERE
Year([data_field]) Not In (select filter_field from filter);

Cheers,
Jason Lepack

Hello, I need help with this problem. I have two lists of dates. I am
using a table as a filter for this list. What I want to do is create a
final listing of all dates that are less than the minimum date in the
filter. This is probably easier to demonstrate than to explain:


Essentially, I want to display the MAX value from Data that's year is
less than the minimum value of Filter(Table). The result should be
12/31/2006
Click to expand...
Thanks!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks, thats great. I didnt think of this, and so I may need to
redesign some thing. Hopefully won't be back with another question,
but I may :) Thank you
 
Sorry about the frustration earlier, dealing with something else as
well.

Don't hesitate to post back if you have any other questions.

Cheers,
Jason Lepack

SELECT
Max(data_field) AS max_date
FROM
data
WHERE
Year([data_field]) Not In (select filter_field from filter);
Click to expand...
Cheers,
Jason Lepack
Click to expand...
- Show quoted text -
Click to expand...

Thanks, thats great. I didnt think of this, and so I may need to
redesign some thing. Hopefully won't be back with another question,
but I may :) Thank you- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top