A simple query problem

[Rex]

Hi,

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.

 

[Allen Browne]

Copy the MaxOfList() function from:
http://allenbrowne.com/func-09.html

You can then type an expression like this into the last column in your
query:
LatestDate: MaxOfList([date1],[date2],[date3],[date4])

You may already be aware that this is not the correct way to design a
relational database. Whenever you see repeating fields such as Date1, Date2,
.... it always means you need a related table that has lots of records
instead of this wide table with lots of fields.

 

[Guest]

Is there any way that you can normalize your table so that each different
date creates a record in a related table? Then a simple query would find the
max date.

If you can't normalize, you could create a union query like:

SELECT ID, 1 as DateNum, [Date1] As TheDate
FROM tblNoNameGiven
UNION ALL
SELECT ID, 2, [Date2]
FROM tblNoNameGiven
UNION ALL
SELECT ID, 3, [Date3]
FROM tblNoNameGiven
UNION ALL
SELECT ID, 4, [Date4]
FROM tblNoNameGiven;

You could then create a query like:
SELECT ID, Max(TheDate) as MaxDate
FROM quniNormalized
GROUP BY ID;

 

[Rex]

Hi, I also want to know get the name of the column for which the date
is Max.. so basically in the example it would also have an extra column
which would specify which one is it..

ID | date1 | date2 | date3 | date4 | latestDate
| whichone?

-----------------------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06 |
date3

cheers
Rex

Copy the MaxOfList() function from:
http://allenbrowne.com/func-09.html

You can then type an expression like this into the last column in your
query:
LatestDate: MaxOfList([date1],[date2],[date3],[date4])

You may already be aware that this is not the correct way to design a
relational database. Whenever you see repeating fields such as Date1, Date2,
... it always means you need a related table that has lots of records
instead of this wide table with lots of fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.

 

[Rex]

Hi, I also want to get the name of the column for which the date is
Max.. so basically in the example it would also have an extra column
which would specify which one is it..

ID | date1 | date2 | date3 | date4 | latestDate
| whichone?

-----------------------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06 |
date3

cheers
Rex

Copy the MaxOfList() function from:
http://allenbrowne.com/func-09.html

You can then type an expression like this into the last column in your
query:
LatestDate: MaxOfList([date1],[date2],[date3],[date4])

You may already be aware that this is not the correct way to design a
relational database. Whenever you see repeating fields such as Date1, Date2,
... it always means you need a related table that has lots of records
instead of this wide table with lots of fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.

 

[Allen Browne]

Again, you will need to write code that loops through the values, selects
the largest value, and then identifies the name of the field that contained
that.

I don't have an example for you to copy. I honestly think you would be
better spending your time creating a related table to put these values in.
The table would have fields:
NewID AutoNumber
ID relates to the ID of your exising table (so one ID can have
many dates)
DateTypeID Drop-down list to choose"Date1", "Date2", or whatever
these are.
TheDate Date/Time field holding the value.

You can then create a query that selects the most recent record by date and
for each ID by using a Totals query.

Since you need the DateTypeID matching the latest date, this article
explains 4 ways to get that:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I also want to get the name of the column for which the date is
Max.. so basically in the example it would also have an extra column
which would specify which one is it..

ID | date1 | date2 | date3 | date4 | latestDate
| whichone?

-----------------------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06 |
date3

cheers
Rex

Copy the MaxOfList() function from:
http://allenbrowne.com/func-09.html

You can then type an expression like this into the last column in your
query:
LatestDate: MaxOfList([date1],[date2],[date3],[date4])

You may already be aware that this is not the correct way to design a
relational database. Whenever you see repeating fields such as Date1,
Date2,
... it always means you need a related table that has lots of records
instead of this wide table with lots of fields.

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.