Maximum date query

[GaryJ]

Hi

I have 5 different fields that contain dates of different stages of a training process.

What I need is query that returns the latest date accross 5 fields

So I need a query that tells me the lastest date from the fields, stage 1, stage 2, stage 3 etc

If stage 1contained 28,09,09, and stage 2 contained 29,09,09 then I want the query to return "29,09,09"

I hope that makes sense, I have attached the sql view, however this just contains the fields I have selected

SELECT Details.[Learner ID], Details.First, Details.Last, Details.[Current Stage], Details.[Stage 1], Details.[Stage 2], Details.[Stage 3], Details.[Stage 4], Details.[Stage 5]
FROM Details
GROUP BY Details.[Learner ID], Details.First, Details.Last, Details.[Current Stage], Details.[Stage 1], Details.[Stage 2], Details.[Stage 3], Details.[Stage 4], Details.[Stage 5];

 

[Kryst51]

I venture (maybe wrongly) to say that you have a normalization problem. It sounds like your table structure is like this:

Table Details
LearnerID
First
Last
Current Stage
Stage 1
Stage 2
Stage 3
Stage 4
Stage 5

in which each of the stage fields contains a date....

You might want to consider this:

Table Details
DetailID
LearnerID (this might replace the Detail ID if it is the unique, primary key of this table, but I don't know that)
First
Last

Table DetailsStages (This is the junction table between the details and the stages)
DetailStageID
StageID
DetailID
SDDate (Don't just say date as it is a reserved word in Access)

Table Stages
StageID
Stage Name

Then you can build a form with a subform for easy entry. Also this will make querying the dates the way you want much easier. Also, if you ever need to add a stage, you won't have to change your table structure.

When you build your query from this table structure, in the date field in the QBE you would maybe use "MAX" or some other such function, not entirely sure.