Jump to content
Sign in to follow this  
one09jason

Interesting find problem, need help

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.