Query

[Guest]

Hopefully I will explain this well:

I have records with the following information. I am looking to extract
those document numbers which have records in both 2003 and 2004. i.e ignoring
those document numbers which relate only to one year

Doc No Doc Type Year Amount
12345 OK 2004 10
12345 OK 2003 15
12345 OK 2004 10
67890 OK 2004 5
67890 Ok 2004 10
54321 OK 2003 20

 

[Nikos Yannacopoulos]

Kevin,

Assuming the table is called MyTable, the criterion on the Doc No field
in te query should be:

In (SELECT [Doc No] FROM [MyTable] WHERE [Year] = 2003) AND
In (SELECT [Doc No] FROM [MyTable] WHERE [Year] = 2004)

(all in one line, don't be fooled by wrapping in your newsreader)

The above assumes Year is a numeric field, if text enclose 2003 and 2004
in double quotes (" ").

Note: Year is a reserved keyword in Access, and using it as a field name
may get you into touble. Suggest you change it to something else, e.g.
DocYear.

HTH,
Nikos

 

[Guest]

thank you nikos......great help.

Kevin,

Assuming the table is called MyTable, the criterion on the Doc No field
in te query should be:

In (SELECT [Doc No] FROM [MyTable] WHERE [Year] = 2003) AND
In (SELECT [Doc No] FROM [MyTable] WHERE [Year] = 2004)

(all in one line, don't be fooled by wrapping in your newsreader)

The above assumes Year is a numeric field, if text enclose 2003 and 2004
in double quotes (" ").

Note: Year is a reserved keyword in Access, and using it as a field name
may get you into touble. Suggest you change it to something else, e.g.
DocYear.

HTH,
Nikos

Hopefully I will explain this well:

I have records with the following information. I am looking to extract
those document numbers which have records in both 2003 and 2004. i.e ignoring
those document numbers which relate only to one year

Doc No Doc Type Year Amount
12345 OK 2004 10
12345 OK 2003 15
12345 OK 2004 10
67890 OK 2004 5
67890 Ok 2004 10
54321 OK 2003 20