Help with query!

[Aximboy]

I have a table with Patient's Name, Year Visited, Check-up, Laboratory, X-ray, Dental & Vision columns.

Check-up, Laboratory & X-ray exams are done every year.
Dental and Vision exams are done every three years.

I want to create a report where it will tell the user which test are not done and are due for a specific year. Reports will contain checkboxes to tell the user which tests needs to be done. Sorta like a year-end report.

So, how do I create a query to select all the Year Visited (which the user will input) with blank or $0.00 (on Check-up, Laboratory & X-ray) fields; and also select when the next dental and/or vision exam (based on the last exam + 3 years) for all the Year Visited for every patient.

TIA

 

[Golom]

What sort of data is in the Check-up, Laboratory, X-ray, Dental & Vision columns? Are they dates? Yes/No (i.e. Boolean) fields?

When you say "... 3 years ..." you might mean
[tt]
Last Exam Dec 31, 2003 ---> Next one due Jan 1, 2006 (2006-2003) = 3 Years

OR

Last Exam Dec 31, 2003 ---> Next one Due Dec 1, 2006 (36 Months)

OR

Last Exam Dec 31, 2003 ---> Next one Due Dec 31, 2006 (3 * 365.25 Days)
[/tt]
Which do you want to use?

 

[Aximboy]

Thanks for replying Golom.

Datas in Check-up, Laboratory, X-ray, Dental & Vision columns are all in currencies.
The Year Visited column only contains years (1999, 2000, 2001, etc.), no months or dates. So, what I meant by 3 years is when the last entered data was on 1998, the next exam should be on 2001.

 

[Golom]

Select PatientName, YearVisited, 
       IIF(YearVisited + 1>Year(Date)," ", 
           Format(YearVisited+1,"0")) As [Next CheckUp],
       IIF(YearVisited + 1>Year(Date)," ", 
           Format(YearVisited+1,"0")) As [Next Lab],
       IIF(YearVisited + 1>Year(Date)," ", 
           Format(YearVisited+1,"0")) As [Next XRay],
       IIF(YearVisited + 3>Year(Date)," ", 
           Format(YearVisited+3,"0")) As [Next Dental],
       IIF(YearVisited + 3>Year(Date)," ", 
           Format(YearVisited+3,"0")) As [Next Vision]

From myTable

Where (NZ(CheckUp)    = 0)  
   OR (NZ(Laboratory) = 0) 
   OR (NZ([X-ray])    = 0)
   OR (NZ(Dental)     = 0)
   OR (NZ(Vision)     = 0)

or the union alternative

Select PatientName, YearVisited, "CheckUp" As [TestType],
       YearVisited + 1 As [Next Visit]
from myTable
Where NZ(CheckUp)= 0 AND Year(Date) >= YearVisited + 1

UNION ALL

Select PatientName, YearVisited, "Lab", YearVisited + 1
from myTable
Where NZ(Laboratory) = 0 AND Year(Date) >= YearVisited + 1

UNION ALL

Select PatientName, YearVisited, "XRay", YearVisited + 1
from myTable
Where NZ([X-ray]) = 0 AND Year(Date) >= YearVisited + 1

UNION ALL

Select PatientName, YearVisited, "Dental", YearVisited + 3 
from myTable
Where NZ(Dental) = 0 AND Year(Date) >= YearVisited + 3

UNION ALL

Select PatientName, YearVisited, "Vision", YearVisited + 3 
from myTable
Where NZ(Vision) = 0 AND Year(Date) >= YearVisited + 3