Interesting find problem, need help

[one09jason]

I have a database that monitors people as they enter and leave a building. Each record contains a field for name, time, and one for whether they entered or exited.

How do I search this database to find out when two people are in the building at the same time?

[one09jason]

I thought of another way to ask the question. Maybe this one would be easier to solve:

what individuals were in the building between time1 and time2?

Thanks to anyone who has an idea, i'm really stumped.

[one09jason]

I agree competely. Let's say I do set up the database so that records have name, date, and time of visit only. What I can't figure out is how to find days where two inidividuals have both visited. Or how many times both individuals visited two three days in a row, or two out of five days of interest, etc. It's the find itself I don't know how to build. It seems to me that you can't ask FM to find all records of "Mark" and "Jim" that occur on both "Monday" and "Tuesday", only all records of "Mark" OR "Jim" on "Monday" OR "Tuesday". Am missing something about how to do a find, or get results out of FM?

[Vaughan]

quote:

---

It seems to me that you can't ask FM to find all records of "Mark"

and "Jim" that occur on both "Monday" and "Tuesday", only all records of "Mark" OR "Jim" on "Monday" OR "Tuesday".

---

Oh, but you can. I think it's time to read the manual on performing finds, specifically working with multiple find requests and search miltiple fields at once.

[LiveOak]

I think the point to focus on is clearing each entry record with an exit record. This may be a case where you want to make each transaction a "visit", rather than an "entry" or "exit" event. Then entries and exits would be easier to match. Records with entry info, but no exit info would by definition be "in the building" until cleared by some other method.

-bd

[one09jason]

quote:

---

Originally posted by Vaughan:

Oh, but you can. I think it's time to read the manual on performing finds, specifically working with multiple find requests and search miltiple fields at once.

---

I have read the manual exhaustively, which is why I post the question here. As I understand it, adding additional find requests are treated as logical "OR"s. So, in a searching a database of names and dates, a find with one request with "Jim" in the name field and "7/1" in the date field will produce records of Jim's activity on the 1st. Adding an additional request to the find with "Mark" in the name field and "7/1" in the date field produces records of Jim's OR Mark's activity on the 1st FM return results if EITHER person other visits the buliding on this day. How can I structure a find so that FM only returns a result if both Mark AND Jim visit in the 1st?

The original question, which I would like FM to answer, is:

On how many days (or on which days) do both Mark and Jim visit the building?

[ October 19, 2001: Message edited by: one09jason ]

[The Bridge]

quote:

---

Originally posted by one09jason:

I have read the manual exhaustively, which is why I post the question here. As I understand it, adding additional find requests are treated as logical "OR"s. So, in a searching a database of names and dates, a find with one request with "Jim" in the name field and "7/1" in the date field will produce records of Jim's activity on the 1st. Adding an additional request to the find with "Mark" in the name field and "7/1" in the date field produces records of Jim's OR Mark's activity on the 1st FM return results if EITHER person other visits the buliding on this day. How can I structure a find so that FM only returns a result if both Mark AND Jim visit in the 1st?

The original question, which I would like FM to answer, is:

On how many days (or on which days) do both Mark and Jim visit the building?

[ October 19, 2001: Message edited by: one09jason ]

---

I'm writing off the top of my head here, but try this:

For each visit session record the name, day_in, day_out, time_in and time_out (I know it's not likely that someone will stay overnight, but you never know)

Also for each session create a calculated key, say, cVisitor_In_Out_Key, that will contain, in separate lines, date+time from day_in+time_in to day_out+time_out.

e.g.

If John Smith entered at 3:00 pm on Oct 27 01 and left at 4:30 pm same day, the key would look like this:

102701-1500

102701-1515

102701-1530

102701-1545

102701-1600

102701-1615

For the sake of brevity the example above assumes that you'll round up to the nearest quarter-hour. You can have the key hold each minute between the time_out and time_in values. oAzium's Date & Time plug-in will make this a lot easier!

Create a self-join relationship using this key to see who else was in the building. Or, create a global day and time parent key to perform "finds" in a portal.

Hope this makes sense. Again, off the top of my head. I highly recommend getting the Date & Time plug-in if you're working with scheduling in any way.