Need help with a simple query

[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!

 

[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

 

[S Davis]

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.

 

[Jason Lepack]

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

 

[S Davis]

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

Thanks!- Hide quoted text -

- Show quoted text -

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

 

[Jason Lepack]

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);

Cheers,
Jason Lepack

- Show quoted text -

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 -