SQL - How to make Access understand time-span?

[Jimmy_XistenZ]

Hi!


I have this problem with SQL, I'm writing a query that's supposed to show all people who has status "C" during the year 2009 (I just renamed what it really is, but that could probably just be confusing, this works). People can have status "A", "B" and "C", and I only want the ones who are "C". People can get promoted to "C" from any lower status, and demoted (is that the word?) from C.
I do get people who was "C" before 2009, who became "C" during 2009, but I can't get the people who got demoted from "C" before 2009 and then promoted back to "C" again (before 2009).
Here's the code so far:

 
SELECT DISTINCT fName, lName, email
FROM Person
WHERE pID IN (SELECT Member
                     FROM Status
                     WHERE statusType= 'C' 
                     AND date < #2010-01-01#
                     AND pID NOT IN (SELECT Member
                                             FROM Status
                                             WHERE correction = 'down'
                                             AND date< #2009-01-01#)
                                             OR pID IN (SELECT Member
                                                            FROM Status
                                                            WHERE correction = 'up'
                                                            AND statusType = 'C'
                                                            AND date BETWEEN #2009-01-01# AND #2010-01-01#));

The attributes in the table Status that you need to know about is: "Member", "statusType", "date" and "correction". Every row is updated only when the statusType for a certain member is changed, so an example:

Member      statusType      date                  correction
25             C                   2008-12-03         up
25             A                   2008-12-23         down
25             C                   2008-12-23         up

So as you can see, would I change the date in the last row to "2009-12-23" this person would show up, but how do I tell Access this?
Any hint towards how I could do this is appreciated. Please.

[ajetrumpet]

the table structure may help to figure it out without going back and forth for a while, but that is just IMO.

e.g. - the tables relevant to the query.

[Jimmy_XistenZ]

the table structure may help to figure it out without going back and forth for a while, but that is just IMO.

e.g. - the tables relevant to the query.

I fixed my original post so it's easier to read, I did include how the relevant table is structured hope it helps to help me ^^

[ajetrumpet]

Jimmy, the problem you have here is the fact that there are no 'helper' fields in your table. What you really need to have is, at the very least, a field call 'lStatus' that shows a member's previous status type.

So, say you had a dataset like this (new column added):

Member      statusType      date                  correction            lStatus
25             C                   2008-12-03         up                      B
25             A                   2008-12-23         down                  B
25             C                   2008-12-23         up                      B

...that would make the query job a little easier.

But even if you didn't do that, you can still query your data out by doing this:

SELECT DISTINCT fname, lname, email

FROM person

'first condition
WHERE (year([date]) = 2009 AND statustype = 'c') OR

'second condition
(pid IN (
      SELECT TOP 1 pid FROM person WHERE 
            (year([date]) < 2009 AND statustype = 'C'
                  ORDER BY [DATE] DESC)) AND

(pid IN (
      SELECT TOP 1 pid FROM person WHERE
            (year([date]) > 2009 AND statustype = 'c'
                  ORDER BY [DATE] ASC))))

it is checking for 2 possibilities...

1-if the person sank/rose to the status level 'c' at any 2009 date, AND
2-if the person had a designation of 'c' the entire year of 2009 (if they did, the first sql condition would not catch this).

the 1st condition checks for #1, the second condition checks for #2.

make sense? (oh yeah, and you need to replace all the single quote with doubles. I was thinking php. sorry.

[Jimmy_XistenZ]

I added a column "endDate", using that I fixed the problem very easily thanks