We help IT Professionals succeed at work.

access query

I have imported some rows from a spreasheet. each column is named by date

[15-1-2010], [15-2-2011] and so on for 12 months

code 1
code 2
code 3


how do i create a query that willl tell me which codes are new codes in the following month

in this example codes in 15-2-2010 that do not appear in 15-1-2010. I need to do that for all the months.

regards
Comment
Watch Question

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
It is not clear what your source data looks like or the exact result you are looking for...?

Can you post a sample database to make this all clearer?

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
Retired IT Professional
Commented:
Try this: To my understanding of the issue:
Table a (f1, f2, f3, ....) ' f1, f2 are your date column names
f1      f2      f3
1      1      NULL
2      2      NUll
Null      3      4

Make table a_x query: add union all for 12 values
SELECT  myDate, Code INTO a_x
FROM (SELECT "f1" as myDate, f1 as code from a where f1 >0
UNION ALL SELECT "f2", f2 from a where f2>0
UNION ALL SELECT "f3", f3 from a where f3>0)

Select new codes per period:"which codes are new codes in the following month"
SELECT a.myDate, a.Code
FROM a_x As a
WHERE a.code not in (SELECT b.code from a_x as b WHERE b.myDate<a.myDate)

myDate      Code
f1      1
f1      2
f2      3
f3      4
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
^ check hnasr's post above first...

Author

Commented:
thank you sorry for delay in reply.
Hamed NasrRetired IT Professional

Commented:
Welcome!