This should be simple ...

[mz-ler]

and presumably it's only me who's irgorant.

I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?

Thanks a lot !!

 

[EdFred]

When I ran into this problem, I had to make a query based on the query. Probably not the best way to do it, but with experience at the time. Or what about a filter on the form that displays the information?

 

[mz-ler]

Tried this ...

When I ran into this problem, I had to make a query based on the query. Probably not the best way to do it, but with experience at the time. Or what about a filter on the form that displays the information?

Thanks a lot!!
But the query within the query Access rejects as well. The filter isn't practical as I need the records for further analysis

 

[EdFred]

Are you using the sigma (totals) button in the query, or are you trying to put the min() in the criteria? If you try Min(FieldName) in the Where you get the "Cannot have aggregate function in WHERE clause..." dont you?

 

[neileg]

You can't apply the aggregate function to an alias of a calculated field. So if you have created:
Field3: [Field1]+[Field2]
you can't then use Min([Field3]) in the where clause.

However, you can use Min([Field1]+[Field2])

 

[mz-ler]

My (current) SQL Term

SELECT [CI - Data].v002x, TData.ep,
FROM (Dates INNER JOIN [CI - Data] ON Dates.v002x = [CI - Data].v002x) INNER JOIN TData ON [CI - Data].v001x = TData.v001x
GROUP BY TData.ep
HAVING MIN ([CI - Data].v004x - TData.Date);

v002x is the 'name' for my deadline and ep is one of the variables I want to select. v004x is the date of the deadline and TData.date the submission date.

Thanks for your help

 

[neileg]

That's not the right SQL at all. If you built it in the query grid I think you would have had the right answer. I think this should work (not tested):

SELECT [CI - Data].v002x, TData.ep, MIN ([CI - Data].v004x - TData.Date) as MinOfStuff  
FROM (Dates INNER JOIN [CI - Data] ON Dates.v002x = [CI - Data].v002x) INNER JOIN TData ON [CI - Data].v001x = TData.v001x 
GROUP BY TData.ep;

 

[mz-ler]

Progress

Hi
THANKS.
I used your code. Access now complains that I (have to translate the error message from my German version) try to use expression v002x which is not included in the aggregate function.
Any idea what might cause that?

THANKS

 

[jal]

and presumably it's only me who's irgorant.

I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?

Thanks a lot !!

I'm new at this, but perhaps something like this:
Select Min(DateColumn) From Table1


I'm also wondering if you can do a subquery, something like this:
Select Top 1 Table1.Date From
(Select * FRom Table1
Order By Table1.Date)

 

[mz-ler]

Subquery

Thanks - but Access won't do it that way ....

 

[neileg]

Hi
THANKS.
I used your code. Access now complains that I (have to translate the error message from my German version) try to use expression v002x which is not included in the aggregate function.
Any idea what might cause that?

THANKS

Probably should be:

SELECT [CI - Data].v002x, TData.ep, MIN ([CI - Data].v004x - TData.Date) as MinOfStuff  
FROM (Dates INNER JOIN [CI - Data] ON Dates.v002x = [CI - Data].v002x) INNER JOIN TData ON [CI - Data].v001x = TData.v001x 
GROUP BY [CI - Data].v002x, TData.ep;

Have you tried building this in the query grid?