Re: Query to Find Members of a Group at a Point in Time

Thanks Bob, I'll give that a try.

Is the third query a Union query?

I'm not sure how I would join the in date to the out date in the third
query, as they would be different dates...


On Jan 27, 8:04 pm, Bob Quintal <rquin...@xxxxxxxxxxxxx> wrote:
GM7 <ark...@xxxxxxxxx> wrote in news:abc1f2b1-e16f-4220-b56b-
e106dc15a...@xxxxxxxxxxxxxxxxxxxxxxxxxxx:





All,

I have inherited a DB with a single table showing:
MemberName |Group             |EmailDate|Status, e.g:
FredFlintstone|WaterBuffaloes|01/01/2001|In
FredFlintstone|WaterBuffaloes|02/01/2001|In
FredFlintstone|WaterBuffaloes|03/01/2001|Out
FredFlintstone|WaterBuffaloes|04/01/2001|In
BarneyRubble|WaterBuffaloes|01/01/2001|In
BarneyRubble|WaterBuffaloes|02/01/2001|In
BarneyRubble|WaterBuffaloes|03/01/2001|In
BarneyRubble|WaterBuffaloes|04/01/2001|Out

Assume (ha!) I have all records of when people were in or out of a
group.

I want to write a query that will tell me who was in the Group
"WaterBuffaloes" on 03/15/2001.

Is that possible, given my data set?

Can anyone point me in the right direction?

Thanks

Patrick

First you want to create a pair of queries, one with the earliest in
date for each person, group and another with the person, group and
out date. if a member has multiple in-out pairs it's harder but not
impossible.

Then you create a third query that joins the in date to the out
date, based on the relation between name and group

Finally create a fourth query that filters the third for yourdate
Between indate and outdate.

SQL for the first is
SELECT Group, membername, min(emaildate) as InDate
From table GROUP BY Group, Membername

if you need more, post a followup to this message.

--
Bob Quintal

PA is y I've altered my email address.- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Query to Find Members of a Group at a Point in Time
    ... Is the third query a Union query? ... The NZ is for when there is no out record for a particular member. ...
    (comp.databases.ms-access)
  • Re: Joining two queries of unequal rows
    ... > that are equal to the daily average value, ... > First Query: ... > Unfortunately I cannot get this third query to join the first two at all. ... > It made no difference whatsoever. ...
    (microsoft.public.access.queries)
  • Re: A query from two queries
    ... GROUP BY ItemCode ... FROM QDuplicates as D INNER JOIN QLocations As L ... > Second query find the Items of a location. ... > Third query should find the duplicates of that location and the number of ...
    (microsoft.public.access.queries)
  • Re: Query to Find Members of a Group at a Point in Time
    ... I want to write a query that will tell me who was in the Group ... if a member has multiple in-out pairs it's harder but not ... Then you create a third query that joins the in date to the out ... SELECT Group, membername, minas InDate ...
    (comp.databases.ms-access)
  • Re: sql union self-join syntax
    ... Therefore, this query ... FROM [Labels by Ken] AS LK; ... > MemberName there is no CompanyName and no ContactName, ...
    (microsoft.public.access.queries)