By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

An Odd Relationship

P: 256
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
Dec 12 '06 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,661
The SQL could look something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Serial Number],
  2.        Max(IIf([Status]='Active',[Date],Null)) AS ActiveDate
  3.        Max(IIf([Status]='Waiting For Parts',[Date],Null)) AS WaitingDate
  4.        Max(IIf([Status]='On Hold',[Date],Null)) AS HoldDate
  5.        Max(IIf([Status]='Completed',[Date],Null)) AS DoneDate
  6. FROM tblMain
  7. WHERE [Serial Number]=[Enter Serial Number]
  8. GROUP BY [Serial Number]
You may not need to select by Serial Number as they're already grouped together.
Dec 12 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.