An Odd Relationship

[blyxx86]

I'm designing a new database.

I was writing it all down when I realized I needed to have a very unique (or not so unique) task done with a query, or report.

I am going to have a table called, "tblStatus"

It will contain values like, "Active, Completed, On Hold, Waiting For Parts"

The primary concern here is I will need to be able to display in a report (or query) the difference of dates between a unit that has a status of "On Hold" or "Waiting For Parts" and when it is marked "Completed" within the database.

The units are tracked by a serial number.

For example:
Date Serial Number Status
11/24 00051 On Hold
11/25 00753 Completed
… … …
12/2 00051 Completed

I would need a query, or report to display that on 11/24 the unit with serial 00051 went to status "On Hold" and then on 12/2 the unit with serial 00051 went to status "Completed" and possibly the difference between the two. (However, I know I can find how to find distance between two dates on the forums here so I do not need to fill up the forums with more of that explanation.)

Would it be something like... SELECT * FROM tblMain WHERE ((tblMain.status = "On Hold") AND (tblMain.Status = "Completed")) but only where Serial Number for the one "On Hold" = the Serial Number of the one "Completed"

how would I include a piece that says, ONLY IF Serial Number = Serial Number

[NeoPa]

The SQL could look something like :

SELECT [Serial Number],
       Max(IIf([Status]='Active',[Date],Null)) AS ActiveDate
       Max(IIf([Status]='Waiting For Parts',[Date],Null)) AS WaitingDate
       Max(IIf([Status]='On Hold',[Date],Null)) AS HoldDate
       Max(IIf([Status]='Completed',[Date],Null)) AS DoneDate
FROM tblMain
WHERE [Serial Number]=[Enter Serial Number]
GROUP BY [Serial Number]

You may not need to select by Serial Number as they're already grouped together.