Occurances happening at the same time query

[james.szumigata]

Hello,

This one shows up on access monster as bein solved but it isn't so I
thought that I would repost



Hello,

I have a question that I was hoping that someone could help me with. I

need to know if two events are happening at the same time. For
example:


Joe Smith was working on project A from 1/1/2006 to 1/31/2006 and was
working on Project B in 1/5/2006. I need to know how to identify if
they are working onan two projects at the same time...I know its easy
with 1, but with 50,000 people i need to use a query.


Any help would be greatly appreciated.


Reply »


From: KARL DEWEY - view profile
Date: Mon, Nov 27 2006 1:11 pm
Email: KARL DEWEY <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


How do you record the work?
One record per project with start and end dates?
Or one record per employee per project per day?


- Hide quoted text -
- Show quoted text -

Hello,

I have a question that I was hoping that someone could help me with. I
need to know if two events are happening at the same time. For
example:

Joe Smith was working on project A from 1/1/2006 to 1/31/2006 and was
working on Project B in 1/5/2006. I need to know how to identify if
they are working onan two projects at the same time...I know its easy
with 1, but with 50,000 people i need to use a query.

Any help would be greatly appreciated.

Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Mon, Nov 27 2006 1:54 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


I just need to know the workers name:

for example:


Joe Smith would show up in the query as he is working on project B on a

day within the range he was working on project A



- Hide quoted text -
- Show quoted text -

How do you record the work?
One record per project with start and end dates?
Or one record per employee per project per day?

:

Reply »


From: (e-mail address removed) - view profile
Date: Mon, Nov 27 2006 2:02 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


May be I need to be mopre specific. I have two tables: Workers who
worked on project A and Wrokers who worked on project B. With start
and end dates for all projects. I need to know how many workers in
table B that had a start date between the start and end date on the
project a table?


- Hide quoted text -
- Show quoted text -

I just need to know the workers name:

for example:

Joe Smith would show up in the query as he is working on project B on a
day within the range he was working on project A

Reply »


From: KARL DEWEY - view profile
Date: Mon, Nov 27 2006 2:02 pm
Email: KARL DEWEY <[email protected]>
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I can not tell you how to construct a query if I do not know how you
collect
and store the data.

What is your table structure?



- Hide quoted text -
- Show quoted text -

I just need to know the workers name:

for example:

Joe Smith would show up in the query as he is working on project B on a
day within the range he was working on project A

Reply » Rate this post: Text for clearing space


From: (e-mail address removed) - view profile
Date: Mon, Nov 27 2006 2:07 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Is this what you mean

TABLE A:


Worker Name:
Project: (All A's I separated them out)
Project Start Date
Project End Date


TABLE B:


Worker Name:
Project: (All B's I separated them out)
Project Start Date
Project End Date



- Hide quoted text -
- Show quoted text -

I can not tell you how to construct a query if I do not know how you collect
and store the data.

What is your table structure?

:

Reply »


From: (e-mail address removed) - view profile
Date: Mon, Nov 27 2006 2:27 pm
Email: (e-mail address removed)
Groups: microsoft.public.access.queries
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author


Top


- Hide quoted text -
- Show quoted text -

Is this what you mean

TABLE A:

Worker Name:
Project: (All A's I separated them out)
Project Start Date
Project End Date

TABLE B:

Worker Name:
Project: (All B's I separated them out)
Project Start Date
Project End Date

Reply »

 

[KARL DEWEY]

You need to have your data in ONE table. With the data split you will need
to compare Proj A to Proj B; Proj A to Proj C; Proj A to Proj C; etc.

With the data in one table two queries will do it. Create a table named
CountNumber and a field CountNUM containing numbers 0 (zero) to your maximum
project span.

Ten this first query gives you a list of all project dates.
[JamesProj_A-1] ----
SELECT James.Worker, James.[Start Date]+[CountNUM] AS [Project Start]
FROM James, CountNumber
WHERE ((([James].[Start Date]+[CountNUM])<=[James].[End Date]))
ORDER BY James.Worker, James.[Start Date]+[CountNUM];

This second query finds if there is a worker with duplicate project dates.
SELECT [JamesProj_A-1].Worker, [JamesProj_A-1].[Project Start],
Count([JamesProj_A-1].[Project Start]) AS [CountOfProject Start]
FROM [JamesProj_A-1]
GROUP BY [JamesProj_A-1].Worker, [JamesProj_A-1].[Project Start]
HAVING (((Count([JamesProj_A-1].[Project Start]))>1));