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

GM7 <arkins@xxxxxxxxx> wrote in
news:cbe6c674-3255-4d83-a74d-9d7d344c0067
@v13g2000yqm.googlegroups.co
m:

Thanks Bob, I'll give that a try.

Is the third query a Union query?

no not a union query, a simple left join type

SELECT Query1.Group,
Query1.MemberName,
Query1.InDate,
NZ(Query2.OutDate, date()) as OutDate
from Query1
LEFT JOIN Query2
ON query1.Group = Query2.Group
AND Query1.Membername = Query2.MemberName
WHERE DateInQuestion Between Query1.InDate AND Query2.Outdate

The NZ is for when there is no out record for a particular member.
Define DateInQuestion as your filter parameter in the query
properties.

Hope that helps.

Q

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 -





--
Bob Quintal

PA is y I've altered my email address.
.

Relevant Pages

  • Re: Query to Find Members of a Group at a Point in Time
    ... Is the third query a Union query? ... SELECT Group, membername, minas InDate ...
    (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: Querying AD for Group information (type/scope)
    ... To Query for Distribution groups: ... To query for all users that have some group other than "Domain Users" ... users with primaryGroupID equal to 513 belong to the "Domain Users" group. ... The reason the member attribute of the "Domain users" and some other ...
    (microsoft.public.windows.server.active_directory)