Help with writing Query If Then Condition

[taimysho0]

Hi,

I have two tables

table 1 has a read cycle column and a due date column. Each read read cycle (defined as letters A through Z) has a specific due date for each month.


Ex:
A- 1/20/2012
A- 2/15/2012


B - 1/8/2012
B - 2/19/2012

table two has a request date column displaying the date the user requests a file, and a second column for the read cycle of the request.

ex:
1/17/2012 - A
1/2/2012 - B

i need a to write a query where it looks at the request date of table 2 and looks for a 'due date' from table 1 where the read cycles AND month are equal.



SOmething Like

if the month of the request date from table 2 is the same as the month of the due date from table 1 for that particular cycle (A- Z), then print that particular due date in table 2.

ex:
in the example above, the request on 1/17/2012 for cycle A should display 1/20/2012 for the due date column, since 1/20/2012 is the only due date for that cycle for the month of january. thanks

[aytee111]

I am assuming that you have only one date per month, that if the due date is 1/17 and the request date is 1/25 then the date you need is 1/17. Otherwise these instructions won't work.

Query 1: table 1 - cycle and format(duedate,"mmyyyy") and duedate
query 2: table 2 - cycle and format(requestdate,"mmyyyy")
query 3: join query 1 and 2 on cycle and mmyyyy, use the due date

[taimysho0]

thanks for the help, but what do you mean with the format? where do i put that in the design view of the query? is it a seperate column? thanks!

[aytee111]

Here are the queries - note, I was just messing around with made-up names:

Query4:
SELECT Table2.cycle, Format([requestdate],"mmyyyy") AS MandY, Table2.requestdate FROM Table2;

Query5:
SELECT Table1.cycle, Table1.duedate, Format([duedate],"mmyyyy") AS MandY
FROM Table1;

SELECT Query4.cycle, Query4.requestdate, Query5.duedate
FROM Query4 INNER JOIN Query5 ON (Query4.MandY = Query5.MandY) AND (Query4.cycle = Query5.cycle);

[taimysho0]

you are fantastic thank you