Help with query after date columns combined.

[fezza1]

Hello,

I need some help, so thanks in advance.

My database has 5 columns matching to particular years, i.e.,PrevYr, PrevYr1, PrevYr2, PrevYr3, PrevYr4.

In a simple query, I've combined all 5, to make 1 column, that would look like,

2007 2006 2003 when it outputs.

Now the problem,

I actually only want to see the Maximum year, so for the example above, for the row, just show 2007 (without the 2006 and 2003).

How can I pick out 2007, or more precisely the most current year?

Thanks!
Joe

[Scott Price]

Hello,

I need some help, so thanks in advance.

My database has 5 columns matching to particular years, i.e.,PrevYr, PrevYr1, PrevYr2, PrevYr3, PrevYr4.

In a simple query, I've combined all 5, to make 1 column, that would look like,

2007 2006 2003 when it outputs.

Now the problem,

I actually only want to see the Maximum year, so for the example above, for the row, just show 2007 (without the 2006 and 2003).

How can I pick out 2007, or more precisely the most current year?

Thanks!
Joe

Hi, Joe!

You have a problem arising from an improperly designed database. Have a look at this tutorial: Database Normalisation and Table Structures

When you correctly design your table to put all years into one column, choosing the current year becomes quite simple. DMax([ColumnName]) in the query criteria grid will do the trick.

Using an abnormal design like you have makes things very hard to do (as you are finding).

Regards,
Scott

[fezza1]

Hi,

I did inherit this for what it's worth.

But the reasoning is that each Individual can have multiple years and they wanted to display that as such.

For instance:

Individual PrevYr PrevYr1 PrevYr2
ABC 2007 2006 2005

etc....

Hi, Joe!

You have a problem arising from an improperly designed database. Have a look at this tutorial: Database Normalisation and Table Structures

When you correctly design your table to put all years into one column, choosing the current year becomes quite simple. DMax([ColumnName]) in the query criteria grid will do the trick.

Using an abnormal design like you have makes things very hard to do (as you are finding).

Regards,
Scott

[Scott Price]

I wasn't faulting you personally, just pointing out the pitfalls of a non-normalized database! :-)

The DMax() or Max() function in the query you have already created will give you the maximum value contained in that column.

Regards,
Scott