Comparison of Dates in the same table returning a list

[streetfox]

I have now searched quite a lot but maybe it's my english or can you point me to the correct post?

I have one table with ID and Date
001, 31.12.2011
002, 31.12.2011
002, 1.1.2012
003, 1.1.2012

Now I would like to run an union query based on a form [Old Date], [New Date] which returns a table (ID,Status)
001 Removed
002 BroughtFwd
003 New

I have the idea to do an union query which selects
SELECT All Old
UNION
SELECT All BroughtFwd
UNION
SELECT All New

The Form is easy, but I am not sure how to write the SQL. With EXIST?
Select *, Status As "Old"
FROM (SELECT * FROM Table WHERE Date=[OldDate]) WHERE
EXISTS (SELECT * FROM Table WHERE Date=[NewDate])
UNION
---

I fear this is not going to work and is much too complicated for what I want to accomplish.

 

[jzwp22]

I have one table with ID and Date
001, 31.12.2011
002, 31.12.2011
002, 1.1.2012
003, 1.1.2012

Both 001 and 003 only have 1 record each, so how do you determine if the record is new or to be removed? Is a record considered new because it is occurs in the current year versus the one that occurred last year?

I was thinking that you could use a totals query to get the most recent date for each ID

query name: qryMostRecentDate
SELECT tblMyData.ID, Max(tblMyData.mydate) AS MostRecent
FROM tblMyData
GROUP BY tblMyData.ID;

From there you can create another query that uses the above in combination with a subquery


SELECT qryMostRecentDate.ID, qryMostRecentDate.MostRecent, (SELECT TOP 1 Q1.mydate FROM tblMyData as Q1 WHERE Q1.ID=qryMostRecentDate.ID AND Q1.mydate<qryMostRecentDate.MostRecent ORDER BY Q1.mydate DESC) as PreviousDate
FROM qryMostRecentDate;

If there is no previous data, a null is returned. You could use this to do an evaluation in a calculated field. I will assume that a NEW is a record that has no previous date and the most recent date has a year = current year. With that, I can add a calculated field to the above query that does the evaluation

query name: qryFinal
SELECT qryMostRecentDate.ID, qryMostRecentDate.MostRecent, (SELECT TOP 1 Q1.mydate FROM tblMyData as Q1 WHERE Q1.ID=qryMostRecentDate.ID AND Q1.mydate<qryMostRecentDate.MostRecent ORDER BY Q1.mydate DESC) as PreviousDate, IIF(ISNULL(PreviousDate), IIF(year(qryMostRecentDate.MostRecent)=Year(date()),"NEW","OLD"),"BroughtForward") As Evaluation
FROM qryMostRecentDate;

I've attached a sample database.



By the way, the word Date is a reserved word in Access, so it should not be used as a field or table name.

Also, you will have to be careful with your dates. You might want to check out Allen Browne's site on possible problems with Access and international date formats

 

[streetfox]

Thank you very much. I will try this out as it seems to be much more sophisticated than my current solution. Please see below if you're interested. I leave it to the User of the Form which Periods she wants to compare.

You are correct that an entry can be in different periods and this information is used to compute the changes. e.g.
Entry is present in Period 0 but not in Period 1 => Closed (relative to Period 0)
Entry is present in Period 0 AND in Period 1 => Brought Fwd
Entri is present in Period 1 but not in Period 0 => New (relative to Period 0)

SELECT CallID,'1-Closed' As Status FROM (SELECT CallID FROM qryAuditCallLogAll WHERE Period=[Forms]![frmChanges]![cboFrom])As a  WHERE NOT EXISTS (SELECT CallID FROM qryAuditCallLogAll as b WHERE Period=[Forms]![frmChanges]![cboTo]AND a.CallID=b.CallID)
UNION
SELECT CallID,'0-New' As Status FROM (SELECT CallID FROM qryAuditCallLogAll  WHERE Period=[Forms]![frmChanges]![cboTo]) As a WHERE NOT EXISTS (SELECT CallID FROM qryAuditCallLogAll as b WHERE Period=[Forms]![frmChanges]![cboFrom] AND a.CallID=b.CallID)
UNION
SELECT CallID,'2-BroughtFwd' As Status FROM (SELECT CallID FROM qryAuditCallLogAll WHERE  Period=[Forms]![frmChanges]![cboTo]) As a WHERE EXISTS (SELECT CallID FROM qryAuditCallLogAll as b  WHERE Period=[Forms]![frmChanges]![cboFrom] AND a.CallID=b.CallID);

Thanks again!