Need Help with Query

  • Thread starter Thread starter Veti
  • Start date Start date
V

Veti

I need to look at a table like

Number Created_Date Modified_Date Updated_Date

and in a query I have to find the maximum of the date fields for ever
number.

how do I do this? I am not familiar with VB. in excel I can use a ma
command and specify the columns and I would get the result.

thanks in advance
 
Try creating a new query. Select your 4 fields. Change
the query to a totals query. Leave "group by" under the
Number field, change the "group by" to "max" for your date
fields.
 
Couple ways. One way is to use a UNION query to normalize the data and then use
the aggregate Max function.


SELECT [Number], Created_Date
FROM YourTable
UNION ALL
SELECT [Number], Modified_Date
FROM YourTable
UNION ALL
SELECT [Number], UpDated_Date
FROM YourTable

Save that as Q1

Now using that in a totals query.

SELECT [Number], Max(Created_Date) as MaxDate
FROM Q1


You can do all that in one query, by using the first query as a subquery.

SELECT Tmp.[Number], Max(Tmp.Created_Date) as MaxDate
FROM
[SELECT [Number], Created_Date
FROM YourTable
UNION ALL
SELECT [Number], Modified_Date
FROM YourTable
UNION ALL
SELECT [Number], UpDated_Date
FROM YourTable]. As Tmp
 
Back
Top