Tricky query

[lflopes]

I have one query that shows a NAME for each day.

Every day each NAME must have one and just one SITUATION.

So I'm trying to make a query that shows which have NO SITUAITON yet and other that shows which HAVE A SITUATION already.


It must be simple but I'm out of ideas

Thanks

 

[tkoh78]

Please explain more. I don't understand what you are trying to achieve.

 

[lflopes]

I have a table with NAMES, a table with SITUATION and a table with a daily situation for each NAME

eg
01-09-2006 nameA SitA
01-09-2006 nameB SitA
02-09-2006 namaA SitC
02-09-2006 nameB SitA

For simplifying purposes I want to make a form with 2 two subforms
ONE shows who has not a Situation
TWO shows who has a Situation

so I'll click on subform ONE and it ads that person to subform TWO while making him desapeare from ONE.


I hope I could pass the idea.
Thanks

 

[rburna904]

Lets see if I have your information correct:

Table_A : Name
Table_B : Situation

I would start off by adding a 'Table_C' this should contain your joining information:

Table_C : Table_A.Name(ID) / Table_B.Situation(ID) / Table_C.DateTime

Then you can go and do the following queries:

Select Table_A.Name
FROM Table_A, Table_C
WHERE Table_A.Name(Id) <> Table_C.(Table_A.Name(ID))
AND Table_C.DateTime Between DATE1 and DATE2

This will pull the names of the people who don't have a situation for the specified time frame.

Then to pull the people who do have SITUATIONS do the following
Select Table_A.Name
FROM Table_A
INNER JOIN Table_C ON Table_A.Name(Id) = Table_C.(Table_A.Name(ID))
WHERE Table_C.DateTime Between DATE1 and DATE2

 

[lflopes]

Thanks

rburna904 rules