Query Quandry (A2K)

Topic

New Reply

Hi,
I’m trying to show the status of 2 pieces of equipment. Monitor 1 and Monitor 2.
I have a table that has the fields MonitorNumber(type int) ,SentDate(type Date), Return Date(type Date)
I was trying to use the date fields to show the status.
SentDate ReturnDate
NotNull NotNull = Available
NotNull Null =Out
Null Null =InTransit
I’m just trying to show the latest status of each monitor.

I created a 2 foot long If/Then statement but it would only show the first combination it came across plus
it showed more than one row for the first monitor(1) since it was available and in transit for the new requestor.
This will eventually be on a website that shows the current status of each monitor then they can request me to ship one if its available.
I tried to work oout the logic but it is giving me a headache this late in the afternoon.
Any ideas or modifications that would make this work will be appreciated.
Thanks,
Scott

Reply | Quote

Replies

How do we determine what the latest state is? We can’t use theSentDate or ReturnDate fields, for these can be null.

Reply | Quote

Hans,
I could add a date field and modify the date anytime the record is updated.
I’m not opposed to a total a new approach since this is a new project.

Scott

Reply | Quote

But would a last modified date be useful? What if someone went into the database and edited an older record?

You should think about what you really want to know and whether that can be done using the data you have.

Reply | Quote