Date query logic help

[dgmorr]

Hey guys, I'm a little lost in my logic here for some reason.

I have a table that has 6 dates. I'll label them as 1 to 6.
Date 1 > Date 2 > Date 3 > Date 4 > Date 5 > Date 6.

I would like to run a query to ensure that this holds true. Can anyone give me a little help on this?

Do I have to do an individual compare from 1 field to the 5 other fields 6 times? for example

Select if
Date 1 < Date 2
OR
Date 1 < Date 3
OR
Date 1 < Date 4
etc etc.?

 

[Rich]

Why do you have six separate date fields?

 

[dgmorr]

They are dates relating to different events for each record.

 

[Rich]

Then they should be stored as separate records and not separate fields

 

[dgmorr]

For example, one person has a birthday, first day of school, first day of lost tooth, first graduation. These should be separate record entries?

 

[The_Doc_Man]

In general, principles of normalization suggest that you should split out things that are basically the same because they form what appears to be a repeating group. As a pragmatist, I subscribe to this theory more than half the time. In this case, I definitely subscribe to it.

You keep individual data in a record (say, in tblIndividual) with a prime key such as IndID (individual's ID number).

Then you have a table of dates (say, tblSpecialDates). In the dates table you would have IndID, a date, and a code number. Each record thus identifies a person, a date, and the code identifying the date.

That is the normalized version of this table.

OK, having said this, you CAN do what you are doing but you will forever have more headaches because of it.

If the dates are in a specific order, your current structure can be analyzed via query where if you have six dates, you need five comparison fields. Each field in the query is something like [Date1]<[Date2], [Date2]<[Date3] etc etc. You might also want to look into what you do if a date is missing.

Then when you open the query, you get a NO (or FALSE or 0) in the comparison columsn for each record where one of the comparisons is wrong.

But technically this structure, because it has what appears to be a repeating group, violates proper normalization.