Date/field query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Each record has a name field and seven date/time fields. These fields are
used to record when a review date is. I need to create a query that will
show if any of the date/time fields are older than the current date. The
date/time fields are called 'desreview1' 2 etc. I need to be able to see
which person is out of date on each field. (if all that makes sense)

cheers baz
 
Hi,


Try to first normalize the data:


SELECT name, 1 As Position, desreview1 As desreview FROM myTable
UNION ALL
SELECT name, 2 As Position, desreview2 As desreview FROM myTable
UNION ALL
SELECT name, 3 As Position, desreview3 As desreview FROM myTable
UNION ALL
SELECT name, 4 As Position, desreview4 As desreview FROM myTable
....
UNION ALL
SELECT name, 7 As Position, desreview7 As desreview FROM myTable




Save that as a query, qu1, or make a table out of it.

SELECT DISTINCT name
FROM qu1
WHERE desreview IS NULL

answer to your first question.


SELECT a.name, a.desreview, a.position, b.desreview, b.position
FROM qu1 As a, qu1 As b
WHERE a.position = b.position+1
AND a.desreview < b.desreview

should return the cases where position (i+1) has a date (i+1) occurring
before date of position (i), for the same name.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top