Working out if/when co-occurence, erm, occurs

[dnfish]

Hi guys, new to both this forum and Access in general. Trying to avoid of much as this as possible :banghead: so thought I'd ask some knowledgeable people

I'm using Access to analyse data collected during field work to help my biological research. The Query part of Access should allow us to pull certain things from the database, which is what I'm trying to do here.

I have data describing how a population of crickets use the burrows in their field. Each line contains the year, the ID of the cricket, the ID of the burrow the cricket was using, the start time of this and the end time.

What I want to do is compile a list of instances where there were 2 different crickets in the same burrow. So when there was an overlap of 2 crickets using the same burrow.

I imagine this is analogous to working out if you ever have double booked 2 different clients in the same conference room or similar?

Any help or welcomes will be greatly appreciated

Cheers.

 

[recyan]

Can you elaborate on "the start time of this and the end time".
In case, you have not looked in to, take a look at aggregate queries (GROUP BY).

If possible, post a few dummy data & the result you want out of it.

Thanks

 

[dnfish]

"Start time" is the time and date the cricket (e.g. cricket A) was seen going into the burrow (e.g. burrow 1).

"End time" is the time and date cricket A was seen leaving the burrow 1.

So in between these times we know that Cricket A was in burrow 1.

I've attached a screen shot of the data so you know what format its in.

The output ideally would be a list of "Interactions", giving the ID of the 2 crickets that were in the burrow and the ID of the burrow concerned (and possibly the times/date the crickets were both in the burrow, although that's less important)

Cheers

 

[recyan]

1) Unable to see StartTime & EndTime fields. See only one Date field in the image attched.
2) Why do we have the Year field ?
3) Assuming data like below :

CricketID	BurrowID	StartTime	EndTime
1	A	10/1/12 5:00	10/1/12 7:00
2	A	10/1/12 4:00	10/1/12 6:00
3	B	10/1/12 5:00	10/1/12 9:00
4	A	10/1/12 6:00	10/1/12 8:00
5	C	10/1/12 5:00	10/1/12 9:00

what result would you expect from your query?
or
Have I got the logic of the data wrong ?

Thanks

 

[dnfish]

Ha :muppet: yeah I forgot that file hasn't had the end times added yet, I'm going to add dummy end times to just practise what I'll do when the end times are sorted (someone else is working on that aspect). Sorry for the confusion.

Year is there as in another table just describing the positions of the burrows we have the year recorded, as the burrows are only present for one year.

For the dummy data you posted (assuming I've correctly worked out which times overlap...) ideally I'd get something like this:

Interaction Cricket 1 Cricket 2 Burrow
i 1 2 A
ii 1 3 A

 

[recyan]

Guess, it's a typo in your reply.
In instance ii) you are actually referring to CricketID 4.

Till someone comes along, Just check out if below gives some guidelines :

http://allenbrowne.com/appevent.html

Thanks

 

[dnfish]

Yeah I meant Cricket 4.

Ok cheers for your help

edit: that Allen Browne page looks perfect, so thanks a lot recyan!